SQL Server Pivot后按studentId分组及原理解析

一、Pivot的核心逻辑 #

Pivot的作用是把行数据转成列，背后分三步执行：

• 自动分组：除了指定的聚合列、透视列，剩下的所有列都会被当成分组键
• 聚合计算：对目标列执行你指定的聚合函数（比如max、sum）
• 列转换：把透视列的不同取值，转成结果集中的新列名，填充聚合后的值

你之前的查询出问题，核心是没注意到分组逻辑：原表的id是主键，每一行都唯一，Pivot时会把id和studentId一起当成分组键，导致每个分组只对应一门课的记录，其他课程列自然是null。

二、正确的查询（按studentId聚合） #

要实现每个studentId一行的效果，必须先排除不需要的id列，让Pivot只按studentId分组：

select studentId, [Phy], [Chem], [Math]
from (
    -- 只保留需要的列，排除唯一的id，避免它干扰分组
    select studentId, course, marks
    from Marks
) as src
pivot (
    -- 因为每个学生每门课只有一个分数，max/min/sum都能拿到正确值
    max(marks) 
    for course in ([Phy], [Chem], [Math])
) as pivotTable

执行结果： #

每个studentId对应一行，没有null值（你的测试数据里每个学生都有三门课的成绩，所以结果全是有效值）。

三、原查询的问题拆解 #

原查询直接用Marks整张表做Pivot，id是唯一值，所以分组键是id + studentId，每个分组只有一条记录：

• 比如id=1的分组，只有Phy的分数，Chem和Math列自然是null
• 最终会返回9行数据，大部分列都是null，完全不符合你要的聚合效果

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