基于分区行号实现T-SQL数据透视：不规则分区列转行需求

你需要的是把按CLS.Term_Code和CLS.CRN分区后的多行课程会议记录，行转列为每个重复字段带序号的宽表结构。这里分两种场景给你实现方案：

一、静态实现（已知最大行数，比如你提到的最多11行） #

如果能确定分区内的最大行数（比如11），用条件聚合是最直接且性能稳定的方式。把你的原查询作为子查询，然后按Term_Code和CRN分组，对每个需要转列的字段用CASE WHEN匹配行号生成新列：

WITH meeting_data AS (
    -- 你的原查询，保留rowval和所有需要的字段
    select 
        ROW_NUMBER() over (partition by CLS.Term_Code,CLS.CRN order by dt.MEETING_TYPE_CODE ) as rowval, 
        dt.MEETING_TYPE_CODE,
        CLS.TERM_CODE, 
        cls.[SUBJECT_CODE] + cls.[COURSE_NUMBER] SEC_COURSE_IDENTIFICATION, 
        cls.CRN, 
        loc.BUILDING_CODE as SEC_BUILDING_CODE, 
        loc.ROOM_CODE as SEC_ROOM_CODE, -- 修正原语句的笔误：SEC_RO0M_CODE改为SEC_ROOM_CODE
        ms.SUNDAY_MEETING_IND as TIM_SUNDAY_IND, 
        ms.MONDAY_MEETING_IND as TIM_MONDAY_IND, 
        ms.TUESDAY_MEETING_IND as TIM_TUESDAY_IND, 
        ms.WEDNESDAY_MEETING_IND as TIM_WEDNESDAY_IND, 
        ms.THURSDAY_MEETING_IND as TIM_THURSDAY_IND, 
        ms.FRIDAY_MEETING_IND as TIM_FRIDAY_IND, 
        ms.SATURDAY_MEETING_IND as TIM_SATURDAY_IND, 
        ts.TIME_VALUE as TIME_START, 
        te.TIME_VALUE as TIME_END
    from dbo.F_CLASS_MEETING_TIME mt 
    join dbo.D_CLASS cls on (cls.CLASS_SID = mt.CLASS_SID) 
    join dbo.D_MEETING_SCHEDULE ms on (mt.MEETING_SCHEDULE_SID = ms.MEETING_SCHEDULE_SID) 
    join dbo.D_CAMPUS_LOCATION loc on (mt.CAMPUS_LOCATION_SID = loc.CAMPUS_LOCATION_SID) 
    join dbo.D_MEETING_DETAIL dt on (mt.MEETING_DETAIL_SID = dt.MEETING_DETAIL_SID) 
    join dbo.D_TIME ts on (ts.TIME_SID = mt.START_TIME_SID) 
    join dbo.D_TIME te on (te.TIME_SID = mt.END_TIME_SID)
)
SELECT 
    TERM_CODE,
    SEC_COURSE_IDENTIFICATION,
    CRN,
    -- 转列MEETING_TYPE_CODE
    MAX(CASE WHEN rowval = 1 THEN MEETING_TYPE_CODE END) AS MEETING_TYPE_CODE1,
    MAX(CASE WHEN rowval = 2 THEN MEETING_TYPE_CODE END) AS MEETING_TYPE_CODE2,
    MAX(CASE WHEN rowval = 11 THEN MEETING_TYPE_CODE END) AS MEETING_TYPE_CODE11,
    -- 转列SEC_BUILDING_CODE
    MAX(CASE WHEN rowval = 1 THEN SEC_BUILDING_CODE END) AS SEC_BUILDING_CODE1,
    MAX(CASE WHEN rowval = 2 THEN SEC_BUILDING_CODE END) AS SEC_BUILDING_CODE2,
    MAX(CASE WHEN rowval = 11 THEN SEC_BUILDING_CODE END) AS SEC_BUILDING_CODE11,
    -- 转列SEC_ROOM_CODE
    MAX(CASE WHEN rowval = 1 THEN SEC_ROOM_CODE END) AS SEC_ROOM_CODE1,
    MAX(CASE WHEN rowval = 2 THEN SEC_ROOM_CODE END) AS SEC_ROOM_CODE2,
    MAX(CASE WHEN rowval = 11 THEN SEC_ROOM_CODE END) AS SEC_ROOM_CODE11,
    -- 转列周几标识（以周日为例，其他字段按此格式补充）
    MAX(CASE WHEN rowval = 1 THEN TIM_SUNDAY_IND END) AS TIM_SUNDAY_IND1,
    MAX(CASE WHEN rowval = 2 THEN TIM_SUNDAY_IND END) AS TIM_SUNDAY_IND2,
    MAX(CASE WHEN rowval = 11 THEN TIM_SUNDAY_IND END) AS TIM_SUNDAY_IND11,
    -- 转列时间字段
    MAX(CASE WHEN rowval = 1 THEN TIME_START END) AS TIME_START1,
    MAX(CASE WHEN rowval = 2 THEN TIME_START END) AS TIME_START2,
    MAX(CASE WHEN rowval = 11 THEN TIME_START END) AS TIME_START11,
    MAX(CASE WHEN rowval = 1 THEN TIME_END END) AS TIME_END1,
    MAX(CASE WHEN rowval = 2 THEN TIME_END END) AS TIME_END2,
    MAX(CASE WHEN rowval = 11 THEN TIME_END END) AS TIME_END11
    -- 周一到周六的标识字段，按照上面的格式补充即可
FROM meeting_data
GROUP BY TERM_CODE, SEC_COURSE_IDENTIFICATION, CRN
ORDER BY TERM_CODE, CRN;

说明： #

• 用MAX()聚合是因为每个rowval在分组内唯一，CASE WHEN只会返回对应行的值，其他行都是NULL，聚合后就能得到正确的单个值；
• 所有需要转列的字段都按照CASE WHEN + MAX的格式复制到第11行即可。

二、动态实现（行数不固定，自动适应最大行号） #

如果分区内的行数可能变化（比如以后超过11行），可以用动态SQL自动生成所有需要的列，不需要手动写重复代码：

DECLARE @max_rowval INT, @sql NVARCHAR(MAX);

-- 先获取分区内的最大行号
SELECT @max_rowval = MAX(rowval)
FROM (
    select ROW_NUMBER() over (partition by CLS.Term_Code,CLS.CRN order by dt.MEETING_TYPE_CODE ) as rowval
    from dbo.F_CLASS_MEETING_TIME mt 
    join dbo.D_CLASS cls on (cls.CLASS_SID = mt.CLASS_SID) 
    join dbo.D_MEETING_DETAIL dt on (mt.MEETING_DETAIL_SID = dt.MEETING_DETAIL_SID)
) t;

-- 拼接动态SQL语句
SET @sql = N'
WITH meeting_data AS (
    select 
        ROW_NUMBER() over (partition by CLS.Term_Code,CLS.CRN order by dt.MEETING_TYPE_CODE ) as rowval, 
        dt.MEETING_TYPE_CODE,
        CLS.TERM_CODE, 
        cls.[SUBJECT_CODE] + cls.[COURSE_NUMBER] SEC_COURSE_IDENTIFICATION, 
        cls.CRN, 
        loc.BUILDING_CODE as SEC_BUILDING_CODE, 
        loc.ROOM_CODE as SEC_ROOM_CODE, 
        ms.SUNDAY_MEETING_IND as TIM_SUNDAY_IND, 
        ms.MONDAY_MEETING_IND as TIM_MONDAY_IND, 
        ms.TUESDAY_MEETING_IND as TIM_TUESDAY_IND, 
        ms.WEDNESDAY_MEETING_IND as TIM_WEDNESDAY_IND, 
        ms.THURSDAY_MEETING_IND as TIM_THURSDAY_IND, 
        ms.FRIDAY_MEETING_IND as TIM_FRIDAY_IND, 
        ms.SATURDAY_MEETING_IND as TIM_SATURDAY_IND, 
        ts.TIME_VALUE as TIME_START, 
        te.TIME_VALUE as TIME_END
    from dbo.F_CLASS_MEETING_TIME mt 
    join dbo.D_CLASS cls on (cls.CLASS_SID = mt.CLASS_SID) 
    join dbo.D_MEETING_SCHEDULE ms on (mt.MEETING_SCHEDULE_SID = ms.MEETING_SCHEDULE_SID) 
    join dbo.D_CAMPUS_LOCATION loc on (mt.CAMPUS_LOCATION_SID = loc.CAMPUS_LOCATION_SID) 
    join dbo.D_MEETING_DETAIL dt on (mt.MEETING_DETAIL_SID = dt.MEETING_DETAIL_SID) 
    join dbo.D_TIME ts on (ts.TIME_SID = mt.START_TIME_SID) 
    join dbo.D_TIME te on (te.TIME_SID = mt.END_TIME_SID)
)
SELECT 
    TERM_CODE,
    SEC_COURSE_IDENTIFICATION,
    CRN,' + CHAR(13) + CHAR(10);

-- 循环生成所有字段的转列语句
DECLARE @i INT = 1;
WHILE @i <= @max_rowval
BEGIN
    -- 生成MEETING_TYPE_CODE的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN MEETING_TYPE_CODE END) AS MEETING_TYPE_CODE' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成SEC_BUILDING_CODE的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN SEC_BUILDING_CODE END) AS SEC_BUILDING_CODE' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成SEC_ROOM_CODE的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN SEC_ROOM_CODE END) AS SEC_ROOM_CODE' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成周日标识的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN TIM_SUNDAY_IND END) AS TIM_SUNDAY_IND' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成周一到周六标识的列（示例写周一，其他同理复制）
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN TIM_MONDAY_IND END) AS TIM_MONDAY_IND' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成开始时间的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN TIME_START END) AS TIME_START' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    -- 生成结束时间的列
    SET @sql += N'    MAX(CASE WHEN rowval = ' + CAST(@i AS NVARCHAR) + N' THEN TIME_END END) AS TIME_END' + CAST(@i AS NVARCHAR) + N',' + CHAR(13) + CHAR(10);
    
    SET @i += 1;
END;

-- 去掉最后一个多余的逗号，补充GROUP BY和ORDER BY
SET @sql = LEFT(@sql, LEN(@sql) - 3) + CHAR(13) + CHAR(10) + N'
FROM meeting_data
GROUP BY TERM_CODE, SEC_COURSE_IDENTIFICATION, CRN
ORDER BY TERM_CODE, CRN;';

-- 执行动态SQL
EXEC sp_executesql @sql;

说明： #

• 动态SQL会先查询当前数据里的最大行号，然后自动生成对应数量的列；
• 要注意把周一到周六的字段都补充到循环里，上面的代码只示例了周日和周一；
• 动态SQL灵活性更高，但调试起来比静态SQL稍麻烦。

内容的提问来源于stack exchange，提问作者R.Merritt