SQL Create Column Headers by Month ID-CodePudding

I am trying to extract itemised sales data for the past 12 months and build a dynamic table with column headers for each month ID. Extracting the data as below works, however when I get to the point of creating a SUM column for each month ID, I get stuck. I have tried to find similar questions but I'm not sure of the best approach.

Select Item, Qty, format(Transaction Date,'MMM-yy')
from Transactions

Data Extract:

Item	Qty	Month ID
A123	50	Apr-22
A123	30	May-22
A123	50	Jun-22
A321	50	Apr-22
A999	25	May-22
A321	10	Jun-22

Desired Output:

Item	Apr-22	May-22	Jun-22
A123	50	30	50
A321	50	Null	10
A999	Null	25	Null

Any advice would be greatly appreciated.

CodePudding user response：

This is a typical case of pivot operation, where you

first filter every value according to your "Month_ID" value
then aggregate on common "Item"

WITH cte AS (
    SELECT Item, Qty, FORMAT(Transaction Date,'MMM-yy') AS Month_ID
    FROM Transactions
)
SELECT Item, 
       MAX(CASE WHEN Month_ID = 'Apr-22' THEN Qty END) AS [Apr-22],
       MAX(CASE WHEN Month_ID = 'May-22' THEN Qty END) AS [May-22],
       MAX(CASE WHEN Month_ID = 'Jun-22' THEN Qty END) AS [Jun-22]
FROM cte
GROUP BY Item

Note: you don't need the SUM as long as there's only one value for each couple <"Item", "Month-Year">.