我正在尝试将一个客户的多行交付计划表“扁平化”为每个客户一行。每个客户可以有 1 到 7 个 LeadDays、OrderDays 和 DeliveryDays。
这是我必须处理的：
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 | | | | | |
我知道它应该是一个（相对简单的？）PIVOT 表，但我似乎无法理解它。
您可以使用MAX(CASE比PIVOT. 在您的情况下，您首先需要生成一个行号以进行透视
MAX(CASE
PIVOT
SELECT CustomerNumber, Company, Year, WeekNumber, MAX(CASE WHEN rn = 1 THEN LeadDays END) LeadDays_1, MAX(CASE WHEN rn = 1 THEN OrderDate END) OrderDate_1, MAX(CASE WHEN rn = 1 THEN DeliveryDate END) DeliveryDate_1, MAX(CASE WHEN rn = 2 THEN LeadDays END) LeadDays_2, MAX(CASE WHEN rn = 2 THEN OrderDate END) OrderDate_2, MAX(CASE WHEN rn = 2 THEN DeliveryDate END) DeliveryDate_2, MAX(CASE WHEN rn = 3 THEN LeadDays END) LeadDays_3, MAX(CASE WHEN rn = 3 THEN OrderDate END) OrderDate_3, MAX(CASE WHEN rn = 3 THEN DeliveryDate END) DeliveryDate_3, MAX(CASE WHEN rn = 4 THEN LeadDays END) LeadDays_4, MAX(CASE WHEN rn = 4 THEN OrderDate END) OrderDate_4, MAX(CASE WHEN rn = 4 THEN DeliveryDate END) DeliveryDate_4, MAX(CASE WHEN rn = 5 THEN LeadDays END) LeadDays_5, MAX(CASE WHEN rn = 5 THEN OrderDate END) OrderDate_5, MAX(CASE WHEN rn = 5 THEN DeliveryDate END) DeliveryDate_6 FROM ( SELECT *, rn = ROW_NUMBER() OVER ( PARTITION BY CustomerNumber, Company, Year, WeekNumber ORDER BY OrderDate, DeliveryDate) FROM YourTable t ) t GROUP BY CustomerNumber, Company, Year, WeekNumber;
dbfiddle
