TSQL - Pivot Table with two dynamic string columns

Question

I have three tables; Items, Weeks, and ItemWeekQty.

Items and Weeks table contain only unique values. ItemWeekQty may, or may not contain at most one combination of an item and week.

I need a query to pivot Items on Weeks and show qty data (if these exist in the ItemWeekQty table). It must be dynamic query , if weeks or items are to be added to the lookup tables.

See examples below:

SELECT ItemId FROM Items

--Example Resultset:
--'a'
--'b'
--'c'
--(ItemId is unique)

SELECT YearWeekId, YearWeek FROM Weeks
ORDER BY WeekId

--Example Resultset:
--1, 'Y_2022_W_17'
--2, 'Y_2022_W_18'
--3, 'Y_2022_W_19'


SELECT YearWeekId, ItemId, Qty FROM ItemWeekQty

--Example Resultset:
--1, 'a', 50
--3, 'b', 10

For the above example, the expected output would be:

           Y_2022_W_17    Y_2022_W_18      Y_2022_W_19
   a           50              
   b                                            10
   c

I have managed to write a query below. But do not know how to make it dynamic. This is the query I have right now for testing:

;WITH Items as
(
SELECT 'a' as itemId union all
SELECT 'b' as itemId union all
SELECT 'c' as itemId 
),
Weeks as
(
-- Weeks will be added to the table 
SELECT 1 as YearWeekId, 'Y_2022_W_17' as YearWeek union all
SELECT 2 as YearWeekId, 'Y_2022_W_18' as YearWeek union all
SELECT 3 as YearWeekId, 'Y_2022_W_19' as YearWeek 
),
ItemWeekQty as
(
SELECT 1 as YearWeekId, 'a' as ItemId, 50 as Qty union all
SELECT 3 as YearWeekId, 'b' as ItemId, 10 as Qty
),
ItemsAndWeeks as
(
SELECT * FROM Items 
    CROSS APPLY 
    (
    Select * FROM Weeks
    ) X
)
,PivotStaging AS
(
SELECT IAW.YearWeekId,IAW.YearWeek,IAW.ItemId, IWQ.Qty FROM ItemsAndWeeks IAW
LEFT JOIN ItemWeekQty IWQ ON IWQ.ItemId = IAW.ItemId AND IAW.YearWeekId = IWQ.YearWeekId
)

-- Needs to be dynamic as weeks can change, how to acomplish this?

SELECT 
    ItemId, [Y_2022_W_17],[Y_2022_W_18],[Y_2022_W_19] 
FROM 
    PivotStaging
PIVOT  
(
  SUM(Qty)
  FOR   
YearWeek 
    IN ([Y_2022_W_17],[Y_2022_W_18],[Y_2022_W_19])  
) AS Pvt

user_1863229 · Answer

I suggest not using the PIVOT operator and instead just use an ANSI compliant pivot query here:

SELECT
    i.ItemId,
    MAX(CASE WHEN W.YearWeek = 'Y_2022_W_17' THEN iwq.Qty END) AS Y_2022_W_17,
    MAX(CASE WHEN W.YearWeek = 'Y_2022_W_18' THEN iwq.Qty END) AS Y_2022_W_18,
    MAX(CASE WHEN W.YearWeek = 'Y_2022_W_19' THEN iwq.Qty END) AS Y_2022_W_19
FROM Items i
LEFT JOIN ItemWeekQty iwq ON iwq.ItemId = i.ItemId
LEFT JOIN Weeks w ON w.YearWeekId = iwq.YearWeekId
GROUP BY i.ItemId
ORDER BY i.ItemId;

Often the above version will run faster than using PIVOT, plus it can be easier to read and maintain.

TSQL - Pivot Table with two dynamic string columns

1 Answers