I am trying to "flatten" a delivery schedule table from many rows for one customer into one row per customer. Each customer can have from 1 to 7 LeadDays, OrderDays, and DeliveryDays.
This is what I have to work with:
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
.....and this is what I need it to look like:
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 | | | | | |
I know it should be a (relatively simple?) PIVOT table, but I can't seem to wrap my head around it.
You can do conditional aggregation using MAX(CASE which is much more flexible than PIVOT. In your case, you first need to generate a row-number to pivot over
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;