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.
>Solution :
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">.