SQL Server 2017 TSQL实现多行数据透视为扁平化表

需求说明 #

将单个客户的多行配送记录（包含LeadDays、OrderDate、DeliveryDate字段）转换为每行对应一个客户的结构，每个客户最多保留7组配送数据。

源数据表 #

CustomerNumber | Company | Year | WeekNumber | OrderDate  | OrderDayName | LeadDays | DeliveryDate | DeliveryDayName
--------------------------------------------------------------------------------------------------------------
5002           | Comp_A  | 2022 |     15     |  2022-04-03 |  Sunday      |   1.0    |  2022-04-04  | Monday
5002           | Comp_A  | 2022 |     15     |  2022-04-04 |  Monday      |   1.0    |  2022-04-05  | Tuesday
5002           | Comp_A  | 2022 |     15     |  2022-04-05 |  Tuesday     |   1.0    |  2022-04-06  | Wednesday
5002           | Comp_A  | 2022 |     15     |  2022-04-06 |  Wednesday   |   1.0    |  2022-04-07  | Thursday
5002           | Comp_A  | 2022 |     15     |  2022-04-07 |  Thursday    |   1.0    |  2022-04-08  | Friday
5002           | Comp_A  | 2022 |     15     |  2022-04-08 |  Friday      |   1.0    |  2022-04-09  | Saturday
5002           | Comp_A  | 2022 |     15     |  2022-04-09 |  Saturday    |   1.0    |  2022-04-10  | Sunday
310365         | Comp_A  | 2022 |     15     | 2022-04-05 |  Tuesday     |   1.0    |  2022-04-06  | Wednesday
310365         | Comp_A  | 2022 |     15     | 2022-04-07 |  Thursday    |   1.0    |  2022-04-08  | Friday
310428         | Comp_A  | 2022 |     15     | 2022-04-06 |  Wednesday   |   1.0    |  2022-04-07  | Thursday
19401          | Comp_B  | 2022 |     15     | 2022-04-04 |  Monday      |   1.0    |  2022-04-05  | Tuesday
19401          | Comp_B  | 2022 |     15     | 2022-04-05 |  Tuesday     |   1.0    |  2022-04-06  | Wednesday
19401          | Comp_B  | 2022 |     15     | 2022-04-06 |  Wednesday   |   1.0    |  2022-04-07  | Thursday
19401          | Comp_B  | 2022 |     15     | 2022-04-07 |  Thursday    |   1.0    |  2022-04-08  | Friday
19401          | Comp_B  | 2022 |     15     | 2022-04-08 |  Friday      |   1.0    |  2022-04-09  | Saturday

目标数据表 #

CustomerNumber | Company | Year | WeekNumber | LeadDays_1 | OrderDate_1 | DeliveryDate_1 | LeadDays_2 | OrderDate_2 | DeliveryDate_2 | LeadDays_3 | OrderDate_3 | DeliveryDate_3 | LeadDays_4 | OrderDate_4 | DeliveryDate_4 | LeadDays_5 | OrderDate_5 | DeliveryDate_5 | LeadDays_6 | OrderDate_6 | DeliveryDate_6 | LeadDays_7 | OrderDate_7 | DeliveryDate_7
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5002           | Comp_A  | 2022 |     15     |   1.0      | 2022-04-03  | 2022-04-04     |   1.0      | 2022-04-04  | 2022-04-05     |   1.0      | 2022-04-05  | 2022-04-06     |   1.0      | 2022-04-06  | 2022-04-07     |   1.0      | 2022-04-07  | 2022-04-08     |   1.0      | 2022-04-08  | 2022-04-09     |   1.0      | 2022-04-09  | 2022-04-10
310365         | Comp_A  | 2022 |     15     |   1.0      | 2022-04-05  | 2022-04-06     |   1.0      | 2022-04-07  | 2022-04-08     |            |             |                |            |             |                |            |             |                |            |             |                |            |             |  
310428         | Comp_A  | 2022 |     15     |   1.0      | 2022-04-06  | 2022-04-07     |            |             |                |            |             |                |            |             |                |            |             |                |            |             |                |            |             |  
19401          | Comp_B  | 2022 |     15     |   1.0      | 2022-04-04  | 2022-04-05     |   1.0      | 2022-04-05  | 2022-04-06     |   1.0      | 2022-04-06  | 2022-04-07     |   1.0      | 2022-04-07  | 2022-04-08     |   1.0      | 2022-04-08  | 2022-04-09     |            |             |                |            |             |

TSQL解决方案 #

以下脚本通过CTE生成组内序号+CASE聚合转列的方式实现需求，逻辑清晰且易于维护：

-- 假设源表名为DeliverySchedule
WITH RankedDeliveryData AS (
    SELECT 
        CustomerNumber,
        Company,
        Year,
        WeekNumber,
        LeadDays,
        OrderDate,
        DeliveryDate,
        -- 按客户+年+周分组，按订单日期排序生成组内序号
        ROW_NUMBER() OVER (PARTITION BY CustomerNumber, Company, Year, WeekNumber ORDER BY OrderDate) AS RowSequence
    FROM DeliverySchedule
)
SELECT 
    CustomerNumber,
    Company,
    Year,
    WeekNumber,
    -- 提取第1组配送数据
    MAX(CASE WHEN RowSequence = 1 THEN LeadDays END) AS LeadDays_1,
    MAX(CASE WHEN RowSequence = 1 THEN OrderDate END) AS OrderDate_1,
    MAX(CASE WHEN RowSequence = 1 THEN DeliveryDate END) AS DeliveryDate_1,
    -- 提取第2组配送数据
    MAX(CASE WHEN RowSequence = 2 THEN LeadDays END) AS LeadDays_2,
    MAX(CASE WHEN RowSequence = 2 THEN OrderDate END) AS OrderDate_2,
    MAX(CASE WHEN RowSequence = 2 THEN DeliveryDate END) AS DeliveryDate_2,
    -- 提取第3组配送数据
    MAX(CASE WHEN RowSequence = 3 THEN LeadDays END) AS LeadDays_3,
    MAX(CASE WHEN RowSequence = 3 THEN OrderDate END) AS OrderDate_3,
    MAX(CASE WHEN RowSequence = 3 THEN DeliveryDate END) AS DeliveryDate_3,
    -- 提取第4组配送数据
    MAX(CASE WHEN RowSequence = 4 THEN LeadDays END) AS LeadDays_4,
    MAX(CASE WHEN RowSequence = 4 THEN OrderDate END) AS OrderDate_4,
    MAX(CASE WHEN RowSequence = 4 THEN DeliveryDate END) AS DeliveryDate_4,
    -- 提取第5组配送数据
    MAX(CASE WHEN RowSequence = 5 THEN LeadDays END) AS LeadDays_5,
    MAX(CASE WHEN RowSequence = 5 THEN OrderDate END) AS OrderDate_5,
    MAX(CASE WHEN RowSequence = 5 THEN DeliveryDate END) AS DeliveryDate_5,
    -- 提取第6组配送数据
    MAX(CASE WHEN RowSequence = 6 THEN LeadDays END) AS LeadDays_6,
    MAX(CASE WHEN RowSequence = 6 THEN OrderDate END) AS OrderDate_6,
    MAX(CASE WHEN RowSequence = 6 THEN DeliveryDate END) AS DeliveryDate_6,
    -- 提取第7组配送数据
    MAX(CASE WHEN RowSequence = 7 THEN LeadDays END) AS LeadDays_7,
    MAX(CASE WHEN RowSequence = 7 THEN OrderDate END) AS OrderDate_7,
    MAX(CASE WHEN RowSequence = 7 THEN DeliveryDate END) AS DeliveryDate_7
FROM RankedDeliveryData
GROUP BY CustomerNumber, Company, Year, WeekNumber
ORDER BY CustomerNumber;

脚本说明 #

1. CTE部分：使用ROW_NUMBER()函数为每个客户在同一Year和WeekNumber下的记录按OrderDate排序，生成组内唯一序号RowSequence，序号范围1-7（超过7的记录会被忽略）。
2. 聚合转列部分：通过CASE语句匹配不同的RowSequence，结合MAX聚合函数将多行数据转换为单行多列结构；没有对应序号的记录会返回NULL，对应目标表中的空值。
3. 分组逻辑：按CustomerNumber、Company、Year、WeekNumber分组，确保每个客户的同一周数据合并为一行。

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