SQL Server使用PIVOT/TRANSPOSE实现行转列的最优方案咨询

方案1：静态PIVOT（问题固定场景） #

适用于提前明确所有要转成列的问题名称的场景，性能最优，语法简单。

你之前的代码存在3处问题：缺少子查询别名、缺少PIVOT结果别名、未在IN列表中添加第二个问题。修正后的完整代码如下：

SELECT 
    [ID],
    [accountno],
    [How old are you?],
    [What is your favorite color?]
FROM (
    -- 子查询取出要用到的所有字段
    SELECT ID, accountno, question, answer
    FROM 你的实际表名 -- 替换为你自己的表名
) AS src -- 子查询必须加别名
PIVOT (
    MAX(answer) -- 每个账号对应同一个问题只有1个答案时，用MAX/MIN都不影响结果
    FOR question IN (
        [How old are you?],
        [What is your favorite color?]
    )
) AS piv -- PIVOT运算结果必须加别名

方案2：动态PIVOT（问题不固定场景） #

适用于问题列表会动态新增、不想每次修改SQL结构的场景，会自动读取所有现存问题作为转置后的列名：

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

-- 自动拼接所有去重后的问题作为PIVOT列
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(question) 
    FROM 你的实际表名
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 拼接最终执行的SQL语句
SET @query = 'SELECT [ID], [accountno], ' + @cols + ' 
FROM (
    SELECT ID, accountno, question, answer
    FROM 你的实际表名
) AS src
PIVOT (
    MAX(answer)
    FOR question IN (' + @cols + ')
) AS piv'

-- 执行动态SQL得到结果
EXEC sp_executesql @query

注意事项 #

• 若表中的ID是单条问答记录的唯一主键，同一个accountno下的多条问答ID不同，需要从查询字段中移除ID，或改为取MAX(ID)/MIN(ID)，否则转置后同一个accountno会生成多条记录。
• 若同一个accountno+question存在多条答案，可根据业务需要调整聚合函数，比如用STRING_AGG(answer, ',')拼接多个答案（SQL Server 2017及以上版本支持）。

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