SQL Server中如何实现表数据行转列 完成表1到表2的结构转置

实现思路 #

因为要匹配原表行数动态生成列，需要结合动态SQL+UNPIVOT+PIVOT三个能力实现：

• 先给原表每行生成唯一序号，对应后续的列名
• 用UNPIVOT将原表7个字段转为行存储，每行对应「批次序号、字段名、字段值」
• 用PIVOT将批次序号转为动态列，最终得到7行（对应原表7个字段）的结果

完整SQL代码 #

-- 定义动态SQL变量
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 1. 生成动态列名列表：根据原表行数生成[1],[2],...[n]的格式
SELECT @cols = STRING_AGG(QUOTENAME(seq), ',') 
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY BatchNo) seq 
    FROM 你的原表名
) t

-- 2. 拼接完整动态执行SQL
SET @sql = N'
SELECT FieldName, ' + @cols + N'
FROM (
    -- 子查询1：给原表每行加序号，同时将所有字段转成统一字符串类型避免UNPIVOT类型不兼容
    SELECT 
        ROW_NUMBER() OVER(ORDER BY BatchNo) AS seq,
        CAST(BatchNo AS NVARCHAR(MAX)) AS BatchNo,
        CAST(StartDate AS NVARCHAR(MAX)) AS StartDate,
        CAST(StartTime AS NVARCHAR(MAX)) AS StartTime,
        CAST(EndDate AS NVARCHAR(MAX)) AS EndDate,
        CAST(EndTime AS NVARCHAR(MAX)) AS EndTime,
        CAST(RecipeNo AS NVARCHAR(MAX)) AS RecipeNo,
        CAST(Status AS NVARCHAR(MAX)) AS Status
    FROM 你的原表名
) src
-- UNPIVOT把7个字段转成行
UNPIVOT (
    FieldValue FOR FieldName IN (BatchNo, StartDate, StartTime, EndDate, EndTime, RecipeNo, Status)
) unpvt
-- PIVOT把序号转成列
PIVOT (
    MAX(FieldValue) FOR seq IN (' + @cols + N')
) pvt
'

-- 3. 执行动态SQL
EXEC sp_executesql @sql

注意事项 #

• 把代码里的你的原表名替换成你实际的表名即可
• 所有字段统一转成NVARCHAR类型是为了避免UNPIVOT要求所有待转字段类型一致的限制，不影响最终展示效果
• 如果需要自定义列名（比如把[1]改成Batch_1），可以在生成@cols的时候调整拼接逻辑即可

兼容提示：如果使用的是SQL Server 2016及更早版本，不支持STRING_AGG函数，可以替换@cols的生成逻辑为以下代码：

SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY BatchNo))
    FROM 你的原表名
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

内容的提问来源于stack exchange，提问作者Faizul