如何用SQL实现考试表的Unpivot与行对齐？求正确方案

需求说明 #

给定[dbo].[Exam]表（所有字段均为varchar类型），需将按科目（SUBJECT）分散的行数据，转换为每个科目对应的MARKS、COMMENT、TDATE字段排列在同一行的结构。

原表结构及数据 #

目标表结构及数据（注：实际SQL中列名需唯一，此处调整为带科目前缀的合法列名） #

你尝试的错误代码 #

SELECT
             [MATH],
             [ENGLISH],
             [SCIENCE]
         FROM (
             SELECT
                 STD, stdn,
                 cont,
                 x,
                 SUBJECT
             FROM
                 [dbo].[Exam]
             UNPIVOT (
                 x
                 for cont in (COMMENT, TDATE)
             ) a
        ) a
        PIVOT (
           MAX(x) 
           FOR SUBJECT IN (
                [MATH],
                [ENGLISH],
                [SCIENCE],
            )
        ) p
        WHERE p.stdn IN (SELECT STD FROM [dbo].[exam])

正确实现方法 #

方法1：静态列实现（科目固定时使用） #

当科目数量固定、已知时，使用条件聚合的方式最直观且易维护：

SELECT
    STD AS 学生编号,
    -- 数学科目对应字段
    MAX(CASE WHEN SUBJECT = 'MATH' THEN MARKS END) AS MATH,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN COMMENT END) AS MATH_COMMENT,
    MAX(CASE WHEN SUBJECT = 'MATH' THEN TDATE END) AS MATH_TDATE,
    -- 英语科目对应字段
    MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN MARKS END) AS ENGLISH,
    MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN COMMENT END) AS ENGLISH_COMMENT,
    MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN TDATE END) AS ENGLISH_TDATE,
    -- 科学科目对应字段
    MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN MARKS END) AS SCIENCE,
    MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN COMMENT END) AS SCIENCE_COMMENT,
    MAX(CASE WHEN SUBJECT = 'SCIENCE' THEN TDATE END) AS SCIENCE_TDATE
FROM [dbo].[Exam]
GROUP BY STD;

方法2：动态列实现（科目不固定时使用） #

如果科目数量可能新增或变化，可使用动态SQL自动生成目标列：

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

-- 自动生成每个科目对应的三个字段的SQL片段
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN SUBJECT = ''', SUBJECT, ''' THEN MARKS END) AS ', QUOTENAME(SUBJECT), ',',
        'MAX(CASE WHEN SUBJECT = ''', SUBJECT, ''' THEN COMMENT END) AS ', QUOTENAME(SUBJECT + '_COMMENT'), ',',
        'MAX(CASE WHEN SUBJECT = ''', SUBJECT, ''' THEN TDATE END) AS ', QUOTENAME(SUBJECT + '_TDATE')
    ), ','
)
FROM (SELECT DISTINCT SUBJECT FROM [dbo].[Exam]) AS Subjects;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT STD AS 学生编号, ', @cols, ' FROM [dbo].[Exam] GROUP BY STD;');

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

错误代码问题说明 #

你原来的代码存在多处逻辑错误：

• 引用了不存在的字段stdn；
• UNPIVOT仅处理了COMMENT和TDATE，未包含需要转换的MARKS字段；
• PIVOT逻辑未匹配每个科目对应多字段的需求，无法生成目标结构。

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