SQL2017 TSQL Pivot multiple rows to flattenend table-CodePudding

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.

CodePudding user response：

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;

db<>fiddle