Query and Pivot Column for the last last 3 Years SQL

Question

My need is to retrieve sell data from and pivot the last three years and sum the quantities. See example below. I wonder how to do it. I read that SQL pivot is the way to go, but I can't figure out how to deal with pivoting "automatically" N number of years in the past.

INPUT

+----------+----------+------+
| ItemCode | Quantity | Date |
+----------+----------+------+
|     A    |    100   | 2017 |
+----------+----------+------+
|     B    |    200   | 2017 |
+----------+----------+------+
|     B    |    200   | 2017 |
+----------+----------+------+
|     A    |    50    | 2018 |
+----------+----------+------+
|     A    |    170   | 2018 |
+----------+----------+------+
|     A    |    75    | 2019 |
+----------+----------+------+
|     B    |    10    | 2019 |
+----------+----------+------+

OUTPUT

+---+------+------+------+
|   | 2017 | 2018 | 2019 |
+---+------+------+------+
| A |  100 |  220 |  75  |
+---+------+------+------+
| B |  400 |   -  |  10  |
+---+------+------+------+

score 2 · Answer 1 · answered May 28 '20 at 09:54

Building on Venkataraman R answer. If you want the years to be added as they appear you could use a bit of dynamic code:

DROP TABLE IF EXISTS #Temp
CREATE TABLE #temp
    ( ItemCode char(1)
    , Quantity int
    , [Date] int
    )
INSERT INTO #temp
VALUES    ('A',100,2017)
        , ('B',200,2017)
        , ('B',200,2017)
        , ('A',50,2018)
        , ('A',170,2018)
        , ('A',75,2019)
        , ('B',10,2019);

DECLARE 
    @columns nvarchar(MAX) = ''
    , @sql nvarchar(MAX);

SELECT 
    @columns += QUOTENAME([Date]) + ','
FROM (
    SELECT [Date]
    FROM #temp
    GROUP BY [Date]) AS t
ORDER BY 
    [Date];

SET @columns = LEFT(@columns, LEN(@columns) - 1);

PRINT @columns;

SET @sql = '
SELECT 
    *
FROM #temp AS t
PIVOT(
    SUM(Quantity)
    FOR [Date] IN(
    ' + @columns + '
        )) AS pvt'

EXECUTE sp_executesql @sql

score 1 · Answer 2 · answered May 28 '20 at 08:59

Try this below logic-

SELECT ItemCode,
SUM(CASE WHEN Date = 2017 THEN Quantity ELSE 0 END) AS '2017',
SUM(CASE WHEN Date = 2018 THEN Quantity ELSE 0 END) AS '2018',
SUM(CASE WHEN Date = 2019 THEN Quantity ELSE 0 END) AS '2019'
FROM your_table
GROUP BY ItemCode

Venkataraman R · Accepted Answer · 2020-05-28T09:13:39.290

1

You can try PIVOT opertor in SQL Server. I am applying YEAR function to get current year and subtracting 3 years from it to filter the dates.

    DECLARE @table TABLE(Itemcode CHAR(1), Quantity int, datevalue int)

  INSERT INTO @TABLE VALUES
  ('A',100   ,2017 ),
  ('B', 200  ,2017 ),
  ('B', 200  ,2017 ),
  ('A', 50   ,2018 ),
  ('A', 170  ,2018 ),
  ('A', 75   ,2019 ),
  ('B', 10   ,2019 ),
  ('A', 10   , 2015);


  SELECT Itemcode,[2017],[2018],[2019]
  FROM
  (SELECT * FROM @table WHERE datevalue >= (YEAR(GETDATE()) - 3)) as t
  PIVOT
  (
  SUM(Quantity) FOR dateValue IN ([2017],[2018],[2019])
  ) as pvt

+----------+------+------+------+
| Itemcode | 2017 | 2018 | 2019 |
+----------+------+------+------+
| A        |  100 | 220  |   75 |
| B        |  400 | NULL |   10 |
+----------+------+------+------+

edited May 28 '20 at 09:13

answered May 28 '20 at 09:08

Venkataraman R

12,181
2
31
58

Thanks for your feedback. I appreciate, but If possible I would prefer not to input the years in the query to make this logic work without the need of me updating this query every year. – beginnerspirit May 28 '20 at 09:58
dynamic sql is not supported for views, user defined functions. you can go for stored procedures with dynamic SQL. – Venkataraman R May 28 '20 at 11:36

Query and Pivot Column for the last last 3 Years SQL

3 Answers3