如何实现未知行数的SQL表行转列以生成目标报表格式

你需要把#candidates表（结构为LAST_NAME、FIRST_NAME、MIDDLE_NAME，行数不确定）转置为「原列名作为行，原每行数据作为列」的报表格式，之前的UNPIVOT/CROSS APPLY是列转行，手动配置列又不支持未知行数，用动态SQL就能完美解决这个问题。

实现步骤与代码 #

1. 给临时表添加序号列

先给#candidates加一个自增序号，用来唯一标识每一条候选人记录（后续动态生成列需要依赖这个序号）：

ALTER TABLE #candidates ADD Seq INT IDENTITY(1,1);

2. 编写动态SQL生成转置查询

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 动态生成列名（如Candidate1, Candidate2...），自动适配表的行数
SELECT @cols = STRING_AGG(QUOTENAME('Candidate' + CAST(Seq AS VARCHAR)), ', ')
FROM #candidates;

-- 拼接转置逻辑的SQL语句
SET @query = N'
SELECT 
    FieldName,
    ' + @cols + N'
FROM (
    -- 将原表拆分为「字段名-序号-值」的结构，为PIVOT做准备
    SELECT 
        Seq,
        FieldName = ''LAST_NAME'',
        FieldValue = LAST_NAME
    FROM #candidates
    UNION ALL
    SELECT 
        Seq,
        FieldName = ''FIRST_NAME'',
        FieldValue = FIRST_NAME
    FROM #candidates
    UNION ALL
    SELECT 
        Seq,
        FieldName = ''MIDDLE_NAME'',
        FieldValue = MIDDLE_NAME
    FROM #candidates
) AS src
PIVOT (
    MAX(FieldValue)
    FOR Seq IN (' + REPLACE(@cols, 'Candidate', '') + N')
) AS pvt
-- 按原表列顺序排序输出
ORDER BY 
    CASE FieldName
        WHEN ''LAST_NAME'' THEN 1
        WHEN ''FIRST_NAME'' THEN 2
        WHEN ''MIDDLE_NAME'' THEN 3
    END;
';

-- 执行动态生成的SQL
EXEC sp_executesql @query;

兼容低版本SQL Server（2016及更早） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH替代列拼接逻辑：

SELECT @cols = STUFF((
    SELECT ', ' + QUOTENAME('Candidate' + CAST(Seq AS VARCHAR))
    FROM #candidates
    ORDER BY Seq
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

清理（可选） #

转置完成后如果不需要序号列，可以删除：

ALTER TABLE #candidates DROP COLUMN Seq;

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