SQL开发求助：基于Pivot实现行数据转日期-周组合动态列

嘿，我来帮你搞定这个困扰多日的行转列需求！你的目标是把Transaction表中每个SAPID对应每周的周一到周日数值，转成星期几_周数格式的动态列，比如MONDAY_3、TUESDAY_4这种，这个需求需要先拆分行（Unpivot）再动态合并列（Pivot），我们一步步来解决。

先分析你的原SQL问题 #

你之前尝试的SQL有几个关键问题：

• 聚合对象错误：sum(week)是对周数求和，但你实际需要聚合的是周一到周日的数值；
• 缺少Unpivot步骤：Transaction表的周一到周日是列，需要先转成行，才能和周数拼接成目标列名；
• Pivot的IN子句不支持动态子查询：多数SQL数据库（比如SQL Server、Oracle）的静态Pivot要求IN里是固定列名，动态列需要用动态SQL生成。

解决方案 #

步骤1：先拆分Transaction表的列成行 #

首先把Transaction表中每个SAPID的周一到周日列转成(sap_id, week, 星期名称, 数值)的行结构，同时关联Calendar表拿到周数：

SELECT 
    t.sap_id,
    c.week,
    day_name,
    day_value
FROM ssc_transactions t
-- 这里要确保关联条件正确！如果Calendar表存的是周对应的日期范围，需要改成日期关联，比如t.date BETWEEN c.monday AND c.sunday
JOIN ssc_calendar_month_week_map c ON t.week = c.week
WHERE c.month = 'Feb' AND c.year = '2018'
UNPIVOT (
    -- 把周一到周日的数值转成day_value字段
    day_value FOR day_name IN (
        monday AS 'MONDAY',
        tuesday AS 'TUESDAY',
        wednesday AS 'WEDNESDAY',
        thursday AS 'THURSDAY',
        friday AS 'FRIDAY',
        saturday AS 'SATURDAY',
        sunday AS 'SUNDAY'
    )
) unpvt

步骤2：静态Pivot（已知周数时） #

如果已经确定要展示的周数（比如你的例子里是周3和周4），可以直接写静态Pivot语句：

SELECT *
FROM (
    SELECT 
        t.sap_id,
        -- 拼接成目标列名格式：星期_周数
        CONCAT(day_name, '_', c.week) AS pivot_column,
        day_value
    FROM ssc_transactions t
    JOIN ssc_calendar_month_week_map c ON t.week = c.week
    WHERE c.month = 'Feb' AND c.year = '2018'
    UNPIVOT (
        day_value FOR day_name IN (
            monday AS 'MONDAY',
            tuesday AS 'TUESDAY',
            wednesday AS 'WEDNESDAY',
            thursday AS 'THURSDAY',
            friday AS 'FRIDAY',
            saturday AS 'SATURDAY',
            sunday AS 'SUNDAY'
        )
    ) unpvt
) src
PIVOT (
    -- 聚合每天的数值（这里用SUM，如果你需要其他聚合可以改成MAX/MIN等）
    SUM(day_value) FOR pivot_column IN (
        'MONDAY_3' AS MONDAY_3,
        'TUESDAY_3' AS TUESDAY_3,
        'WEDNESDAY_3' AS WEDNESDAY_3,
        'THURSDAY_3' AS THURSDAY_3,
        'FRIDAY_3' AS FRIDAY_3,
        'SATURDAY_3' AS SATURDAY_3,
        'SUNDAY_3' AS SUNDAY_3,
        'MONDAY_4' AS MONDAY_4,
        'TUESDAY_4' AS TUESDAY_4,
        'WEDNESDAY_4' AS WEDNESDAY_4,
        'THURSDAY_4' AS THURSDAY_4,
        'FRIDAY_4' AS FRIDAY_4,
        'SATURDAY_4' AS SATURDAY_4,
        'SUNDAY_4' AS SUNDAY_4
    )
) piv;

步骤3：动态Pivot（周数不固定时） #

如果周数是动态的（比如每月周数不同），需要用动态SQL生成Pivot的列名，这里以SQL Server为例：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 自动生成所有需要的列名：MONDAY_3, TUESDAY_3,...SUNDAY_4
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(day_name, '_', c.week))
                      FROM ssc_calendar_month_week_map c
                      -- 交叉连接星期列表，生成每个周的7天列名
                      CROSS JOIN (VALUES ('MONDAY'),('TUESDAY'),('WEDNESDAY'),('THURSDAY'),('FRIDAY'),('SATURDAY'),('SUNDAY')) AS days(day_name)
                      WHERE c.month = 'Feb' AND c.year = '2018'
                      -- 按周数+星期顺序排序，保证列顺序正确
                      ORDER BY c.week, 
                               CASE day_name 
                                   WHEN 'MONDAY' THEN 1 
                                   WHEN 'TUESDAY' THEN 2 
                                   WHEN 'WEDNESDAY' THEN 3 
                                   WHEN 'THURSDAY' THEN 4 
                                   WHEN 'FRIDAY' THEN 5 
                                   WHEN 'SATURDAY' THEN 6 
                                   WHEN 'SUNDAY' THEN 7 
                               END
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接完整的动态SQL
SET @query = 'SELECT sap_id, ' + @cols + ' 
              FROM (
                    SELECT 
                        t.sap_id,
                        CONCAT(day_name, ''_'', c.week) AS pivot_column,
                        day_value
                    FROM ssc_transactions t
                    JOIN ssc_calendar_month_week_map c ON t.week = c.week
                    WHERE c.month = ''Feb'' AND c.year = ''2018''
                    UNPIVOT (
                        day_value FOR day_name IN (
                            monday AS ''MONDAY'',
                            tuesday AS ''TUESDAY'',
                            wednesday AS ''WEDNESDAY'',
                            thursday AS ''THURSDAY'',
                            friday AS ''FRIDAY'',
                            saturday AS ''SATURDAY'',
                            sunday AS ''SUNDAY''
                        )
                    ) unpvt
                  ) src
              PIVOT (
                  SUM(day_value) FOR pivot_column IN (' + @cols + ')
              ) piv;';

-- 执行动态SQL
EXECUTE sp_executesql @query;

注意事项 #

• 关联条件：请根据实际表结构调整Transaction和Calendar表的关联逻辑，如果Calendar表存储的是周对应的日期范围，需要把ON t.week = c.week改成类似t.transaction_date BETWEEN c.monday_date AND c.sunday_date；
• 聚合函数：如果每个SAPID每周每一天只有一个数值，用SUM或MAX效果一样，根据你的业务需求选择；
• 数据库差异：如果用的是Oracle，动态SQL的写法会略有不同（比如用EXECUTE IMMEDIATE），但核心逻辑都是先Unpivot再动态Pivot。

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