SQL Server中使用PIVOT为何仅返回一行？如何得到预期结果？

为什么PIVOT只返回一行？ #

你的SQL只返回一行的核心原因是缺少分组依据列，再加上聚合函数的特性导致的：

• 原查询的子查询仅包含Name和Profession两列，PIVOT会默认把除聚合列（Name）和透视列（Profession）之外的列作为分组键，但这里没有其他列，所有数据会被归为同一组。
• 使用MAX([Name])聚合时，每个职业下只会保留排序最靠前/靠后的单个值（取决于排序规则），最终自然只会生成一行结果。

如何修改得到预期的多行输出？ #

要实现按职业分组展示多行Name，需要先给每个职业下的记录添加行号，让PIVOT能按行号分组，生成对应行数的结果。具体操作如下：

1. 用ROW_NUMBER()窗口函数，按Profession分组、Name排序，给每条记录分配唯一行号，作为分组依据。
2. PIVOT时以这个行号作为分组键，聚合函数依然用MAX([Name])（同一行号+职业下只有一个Name，MAX不会改变结果）。

修改后的SQL语句：

SELECT Doctor, Engineer, Singer, Player
FROM (
    SELECT 
        [Name], 
        Profession,
        -- 按职业分组生成行号，作为PIVOT的分组依据
        ROW_NUMBER() OVER (PARTITION BY Profession ORDER BY [Name]) AS RowNum
    FROM Occupation
) T1
PIVOT (
    MAX([Name]) 
    FOR Profession IN (Doctor, Engineer, Singer, Player)
) T2

结果说明 #

• 每个职业下的Name会按行号对应到不同行，比如Doctor组的Rakib（行号1）、Arif（行号2）会分别出现在第一行和第二行的Doctor列。
• 没有对应Name的位置会自动填充NULL，完全匹配你想要的预期输出。

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