Pivot Data on A SQL WITH Query

Question

The query below returns the SUM of quantities purchased over the last three years. My attempt was to wrap the whole block of code starting from WITH down to the WHERE of the Purchases query but I can not make the pivot work. I also wonder if the YEAR pivoted columns can be dynamically generated instead of passed using the FOR -> IN Pivot syntax.

WITH Purchases (ItemCode, Quantity, DocDate)  
AS  
(  
    SELECT ItemCode, Quantity, OPOR.DocDate
    FROM OPOR INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry 
    WHERE Canceled = 'N'                       
    GROUP BY OPOR.DocDate,ItemCode,QuantitY
)  
SELECT ItemCode, SUM(Quantity) AS Qty, YEAR(DocDate) AS Year
FROM Purchases
WHERE DocDate > DATEADD(YEAR,-4,GETDATE()) AND ItemCode = 'Apple'
GROUP BY ItemCode, YEAR(DocDate)
ORDER BY ItemCode;

Result

+----------+------+------+
| ItemCode | Qty  | Year |
+----------+------+------+
| Apple    | 2300 | 2017 |
+----------+------+------+
| Apple    | 1600 | 2018 |
+----------+------+------+
| Apple    | 1000 | 2019 |
+----------+------+------+
| Apple    | 1400 | 2020 |
+----------+------+------+

I would like to pivot the year column to get the following result:

+----------+------+------+------+------+
| ItemCode | 2017 | 2018 | 2019 | 2020 |
+----------+------+------+------+------+
| Apple    | 2300 | 1600 | 1000 | 1400 |
+----------+------+------+------+------+

So give it a go and let us know how you get on or if you get stuck. — Dale K, Sep 13 '20 at 09:21
Especially given you have already had an pivot written for you in a previous question, at least try this time. — Dale K, Sep 13 '20 at 09:28

score 1 · Accepted Answer · answered Sep 13 '20 at 09:29

1

You can make use of Pivot function in SQL server:

Demo

SELECT * FROM TABLE1
PIVOT(MIN(QTY) FOR [YEAR] IN ([2017], [2018], [2019], [2020])) PIVOT_TABLE;

answered Sep 13 '20 at 09:29

Atif

2,011
9
23

score 1 · Answer 2 · answered Sep 13 '20 at 11:30

If the OP prefers conditional aggregation to SQL Server PIVOT

SELECT [ItemCode],
  max(case when [YEAR]=2017 then QTY else null end) [2017],
  max(case when [YEAR]=2018 then QTY else null end) [2018],
  max(case when [YEAR]=2019 then QTY else null end) [2019],
  max(case when [YEAR]=2020 then QTY else null end) [2020]
FROM TABLE1
group by [ItemCode];

Pivot Data on A SQL WITH Query

2 Answers2

Linked