SQL Server如何将同Class_DBID的多行同列数据拆分到不同教师列

你可以通过「窗口函数排序+条件聚合」的方式实现同一Class_DBID下的教师数据行转列，完全符合需求的输出效果，具体实现代码如下：

WITH TeacherRank AS (
    SELECT 
        c.[Class_DBID],
        c.[Course_DBID],
        c.[DistrictClassID],
        c.[SectionDescr],
        c.[HomePageURL],
        c.[AvgCalcCd],
        s.DistrictStaffID,
        sc.RoleCd,
        c.[Scale_DBID],
        -- 排序规则：主职（RoleCd='P'）优先级最高排第1位，附加职（RoleCd='A'）按Staff_DBID顺序依次向后排
        ROW_NUMBER() OVER (
            PARTITION BY c.Class_DBID 
            ORDER BY CASE sc.RoleCd WHEN 'P' THEN 1 ELSE 2 END, sc.Staff_DBID
        ) AS rn
    FROM [studentsys].[dbo].[Class] c
    INNER JOIN staffclass sc ON sc.Class_DBID = c.Class_DBID
    INNER JOIN staff s ON s.staff_dbid = sc.Staff_dbid
    INNER JOIN school sch ON sch.School_DBID = s.school_dbid
    WHERE c.DistrictClassID = 'ONMS03712'
)
SELECT 
    Class_DBID,
    Course_DBID,
    DistrictClassID,
    SectionDescr,
    HomePageURL,
    AvgCalcCd,
    Scale_DBID,
    MAX(CASE WHEN rn = 1 THEN DistrictStaffID END) AS Teacher1,
    MAX(CASE WHEN rn = 2 THEN DistrictStaffID END) AS Teacher2,
    MAX(CASE WHEN rn = 3 THEN DistrictStaffID END) AS Teacher3,
    MAX(CASE WHEN rn = 4 THEN DistrictStaffID END) AS Teacher4
FROM TeacherRank
GROUP BY 
    Class_DBID,
    Course_DBID,
    DistrictClassID,
    SectionDescr,
    HomePageURL,
    AvgCalcCd,
    Scale_DBID;

补充说明 #

• 附加职教师的排序规则可自定义：如果需要按DistrictStaffID、入职时间等其他维度排序附加职教师，只需修改ROW_NUMBER()中ORDER BY的第二个排序字段即可。
• 多主职场景兼容：如果同一班级存在多个主职（RoleCd='P'）教师，当前逻辑会取排序后的第一个主职教师作为Teacher1，如有特殊展示需求可调整排序规则或聚合逻辑。

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