SQL Server 2019 多周工作日数据PIVOT行转列实现方案问询

问题原因 #

你当前查询输出错误主要有两个原因：

1. 子查询中包含了dseqkey列，PIVOT操作会将除聚合列PctChg、透视列weekDay之外的所有列作为分组依据，连续的dseqkey会导致每行数据单独分为一组，无法按周聚合。
2. 你查询中引用的表名TEMP_SPX_PCHG2和源表实际名称TEMP_PCHG2不一致。

可行实现方案 #

我们可以先给每一行数据分配所属的周分组ID，再基于周分组做PIVOT转置：

SELECT 
    WeekGroup,
    MIN(SDate) AS WeekStartDate,
    MAX(SDate) AS WeekEndDate,
    [MON], [TUE], [WED], [THU], [FRI]
FROM (
    SELECT 
        SDate, 
        PctChg, 
        LTRIM(weekDay) AS weekDay, -- 处理weekDay字段nchar类型的多余空格
        -- 计算周分组ID：利用dseqkey连续的特性，同一周的记录会得到相同的WeekGroup值
        dseqkey - CASE LTRIM(weekDay)
            WHEN 'MON' THEN 1
            WHEN 'TUE' THEN 2
            WHEN 'WED' THEN 3
            WHEN 'THU' THEN 4
            WHEN 'FRI' THEN 5
        END AS WeekGroup
    FROM TEMP_PCHG2
) AS t
PIVOT(
    MAX(PctChg) 
    FOR weekDay IN (
        [MON], 
        [TUE], 
        [WED], 
        [THU], 
        [FRI]
    )
) AS pivot_table
ORDER BY WeekGroup;

方案说明 #

• 周分组ID的计算逻辑兼容节假日场景，即使当周工作日不足5天也能正确分组，适配dateGap为3的特殊情况
• 输出额外携带了周开始、结束日期，你可以根据实际需求删除不需要的字段
• 子查询中增加了LTRIM处理weekDay的nchar(10)类型多余空格，避免匹配不到透视列的问题

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