如图 用sql行转列 图一原表，图二希望转换后

Question

如图 用sql行转列 图一原表，图二希望转换后,用sql server 进行语句转换。感谢
（不能用 case when 因为日期可能是一个大的范围，图中只是示例）

Answer 1

--建表
CREATE TABLE #tmp
(
    a INT ,
    b VARCHAR(100),
    c INT,
    d DATETIME,
    e NUMERIC(18,2),
    f MONEY,
    g MONEY
)
--插入测试数据
INSERT INTO #tmp values(1,'小小',111,'2020-01-01',10.2,99,88)
INSERT INTO #tmp values(2,'大大',222,'2021-02-10',20.3,66,77)
INSERT INTO #tmp values(3,'中中',333,'2022-03-02',30.5,55,76)
INSERT INTO #tmp values(4,'齐齐',444,'2023-04-20',40.5,85,82)
INSERT INTO #tmp values(5,'琪琪',555,'2024-05-03',50.9,23,10)
INSERT INTO #tmp values(6,'七七',666,'2025-06-04',60.0,11,10)

--动态拼接SQL,先列转行,再行转列
DECLARE @sql VARCHAR(max)=''
DECLARE @where VARCHAR(max)=''

SET @where=(
    SELECT ','+QUOTENAME( ROW_NUMBER() OVER(ORDER BY GETDATE()))  FROM #tmp 
    FOR XML PATH('')
)

SET @sql='SELECT *  FROM 
(
    SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER  BY  y) AS z,id,x,y  FROM 
    (
        SELECT 
        ROW_NUMBER() OVER(ORDER  BY GETDATE()) AS id,
        CONVERT(VARCHAR(4000),a) AS a,
        CONVERT(VARCHAR(4000),b) AS b,
        CONVERT(VARCHAR(4000),c) AS c,
        CONVERT(VARCHAR(4000),CONVERT(VARCHAR(10),d,121)) AS d,
        CONVERT(VARCHAR(4000),e) AS e,
        CONVERT(VARCHAR(4000),f) AS f,
        CONVERT(VARCHAR(4000),g) AS g
        FROM #tmp 
    ) a UNPIVOT 
    (
        x FOR y IN (a,b,c,d,e,f,g)
    ) b
) c
PIVOT
(
    MAX(x)
    FOR id IN ('+STUFF(@where,1,1,'')+')
) d'

EXEC (@sql)


DROP  TABLE #tmp