SQL Server 2019中Pivot函数多值行转列问题求助

你的问题核心是PIVOT默认会按非聚合、非透视列（这里仅IDN）分组，所以每个IDN只会返回一行，MAX聚合只能取到该分组下的最大值。要得到同IDN下每个ITEM对应一行的结果，需要先给每个IDN+SUBJECT组合添加行号，让PIVOT时按IDN+行号分组，从而保留多行数据。

修改后的查询语句 #

SELECT IDN, ENGLISH, MATH
FROM (
  SELECT 
    IDN, 
    SUBJECT, 
    ITEM,
    -- 给每个IDN下的同科目ITEM生成唯一序号
    ROW_NUMBER() OVER(PARTITION BY IDN, SUBJECT ORDER BY ITEM) AS rn
  FROM (
    SELECT '2020' IDN, 'ENGLISH' SUBJECT, 'E101' ITEM UNION ALL
    SELECT '2020' IDN, 'ENGLISH' SUBJECT, 'E102' ITEM UNION ALL
    SELECT '2020' IDN, 'MATH' SUBJECT, 'M101' ITEM UNION ALL
    SELECT '2021' IDN, 'ENGLISH' SUBJECT, 'E101' ITEM UNION ALL
    SELECT '2021' IDN, 'ENGLISH' SUBJECT, 'E102' ITEM UNION ALL
    SELECT '2021' IDN, 'ENGLISH' SUBJECT, 'E103' ITEM
  ) AA
) BB
PIVOT
(
  MAX([ITEM]) FOR [SUBJECT] IN ([ENGLISH], [MATH])
) AS PVT
ORDER BY IDN, rn;

原始数据 #

IDN SUBJECT ITEM
2020    ENGLISH E101
2020    ENGLISH E102
2020    MATH    M101
2021    ENGLISH E101
2021    ENGLISH E102
2021    ENGLISH E103

期望输出（执行修改后语句可得到） #

IDN ENGLISH MATH
2020    E101    M101
2020    E102    NULL
2021    E101    NULL
2021    E102    NULL
2021    E103    NULL

关键说明 #

• ROW_NUMBER() OVER(PARTITION BY IDN, SUBJECT ORDER BY ITEM)：为每个IDN下的同一科目生成递增序号，确保每个ITEM在分组中有唯一标识。
• PIVOT时，分组列变为IDN和rn（隐式分组），因此每个IDN+rn组合会生成一行，从而保留所有ITEM的行转列结果。
• 最后通过ORDER BY IDN, rn保证结果顺序符合预期。

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