SQL Server 2019中行转列实现：按年级展示学生成绩

一、能不能用查询实现？ #

完全可以。SQL Server 2019支持动态SQL和PIVOT透视表功能，刚好能应对科目数量、年级数量未知的场景。

实现思路 #

核心逻辑是针对每个年级单独生成透视查询：先获取该年级的所有科目，再动态把科目转成列，最后关联学生和成绩数据输出结果。

具体代码示例 #

你可以运行下面的动态SQL脚本，直接得到需求中的输出格式：

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @StandardCursor CURSOR
DECLARE @StandardID INT, @StandardName NVARCHAR(50)
DECLARE @Subjects NVARCHAR(MAX)

-- 遍历每个年级
SET @StandardCursor = CURSOR FOR
SELECT ID, Standard FROM Standards

OPEN @StandardCursor
FETCH NEXT FROM @StandardCursor INTO @StandardID, @StandardName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接当前年级的所有科目，转成透视列需要的格式
    SELECT @Subjects = STRING_AGG(QUOTENAME(Subject), ', ')
    FROM Subjects
    WHERE StandardID = @StandardID

    -- 动态构建当前年级的成绩查询语句
    SET @DynamicSQL = N'
    PRINT ''' + @StandardName + '年级：''
    SELECT 
        s.ID AS StudentID,
        s.Name,
        ' + @Subjects + '
    FROM (
        SELECT 
            st.ID,
            st.Name,
            sub.Subject,
            m.Marks
        FROM Students st
        JOIN Marks m ON st.ID = m.StudentID
        JOIN Subjects sub ON m.SubjectID = sub.ID
        WHERE st.StandardID = ' + CAST(@StandardID AS NVARCHAR) + '
    ) src
    PIVOT (
        MAX(Marks)
        FOR Subject IN (' + @Subjects + ')
    ) pvt'

    -- 执行当前年级的查询
    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM @StandardCursor INTO @StandardID, @StandardName
END

CLOSE @StandardCursor
DEALLOCATE @StandardCursor

代码说明 #

• 用游标逐个处理每个年级，确保每个年级的科目列都适配
• STRING_AGG函数（SQL Server 2017及以上支持）把科目名拼成透视需要的格式
• PIVOT把行格式的科目成绩转成列，刚好匹配需求的展示样式
• 执行后会按年级输出对应的结果集，和你要的效果完全一致

二、要不要调整表结构？ #

当前的表结构已经能满足需求，不需要修改。如果追求查询性能，可以做以下优化：

• 给Subjects.StandardID、Students.StandardID、Marks.SubjectID、Marks.StudentID这几个关联字段创建非聚集索引，加快表关联的速度
• 如果年级和科目对应关系很少变动，可以提前创建视图预关联学生、科目、成绩数据，但动态透视还是得靠动态SQL实现

内容的提问来源于stack exchange，提问作者Prithvi Emmanuel Machado