Sql-server – Want to use pivot and lag function together in SQL

sql-server-2012

I have the below data in one table. I want to put that data in columns and then have a difference based on the year.

+---------+--------+------+-------+
| Product | REGION | YEAR | Sales |
+---------+--------+------+-------+
| TEA     | D1     | 2018 | 40    |
| TEA     | D2     | 2018 | 30    |
| TEA     | D3     | 2018 | 20    |
| TEA     | D4     | 2018 | 10    |
| TEA     | D5     | 2018 | 7     |
| COFFEE  | D1     | 2018 | 70    |
| COFFEE  | D2     | 2018 | 100   |
| COFFEE  | D3     | 2018 | 200   |
| COFFEE  | D4     | 2018 | 300   |
| COFFEE  | D5     | 2018 | 400   |
| TOFFEE  | D1     | 2018 | 30    |
| TOFFEE  | D2     | 2018 | 7     |
| TOFFEE  | D3     | 2018 | 400   |
| TOFFEE  | D4     | 2018 | 34    |
| TOFFEE  | D5     | 2018 | 90    |
| TEA     | D1     | 2019 | 1     |
| TEA     | D2     | 2019 | 2     |
| TEA     | D3     | 2019 | 3     |
| TEA     | D4     | 2019 | 4     |
| TEA     | D5     | 2019 | 5     |
| COFFEE  | D1     | 2019 | 6     |
| COFFEE  | D2     | 2019 | 7     |
| COFFEE  | D3     | 2019 | 8     |
| COFFEE  | D4     | 2019 | 9     |
| COFFEE  | D5     | 2019 | 10    |
| TOFFEE  | D1     | 2019 | 11    |
| TOFFEE  | D2     | 2019 | 12    |
| TOFFEE  | D3     | 2019 | 13    |
| TOFFEE  | D4     | 2019 | 14    |
| TOFFEE  | D5     | 2019 | 15    |
| TEA     | D1     | 2020 | 16    |
| TEA     | D2     | 2020 | 17    |
| TEA     | D3     | 2020 | 18    |
| TEA     | D4     | 2020 | 19    |
| TEA     | D5     | 2020 | 20    |
| COFFEE  | D1     | 2020 | 21    |
| COFFEE  | D2     | 2020 | 22    |
| COFFEE  | D3     | 2020 | 23    |
| COFFEE  | D4     | 2020 | 24    |
| COFFEE  | D5     | 2020 | 25    |
| TOFFEE  | D1     | 2020 | 26    |
| TOFFEE  | D2     | 2020 | 27    |
| TOFFEE  | D3     | 2020 | 28    |
| TOFFEE  | D4     | 2020 | 29    |
| TOFFEE  | D5     | 2020 | 30    |
+---------+--------+------+-------+

and I want to have the below output. I am not able to do pivot of the data but not able to use lag(if any other function someone can suggest) on rows I moved to columns.
below is the expected output

+---------+------+----------------------------+--------------------------------------+
|         |      |         Department         | Difference in year   department wise |
+---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+
| Product | Year | D1 | D2  | D3  | D4  | D5  | D1D   | D2D  | D3D   | D4D   | D5D   |
+---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+
| TEA     | 2018 | 40 | 30  | 20  | 10  | 7   | 40    | 30   | 20    | 10    | 7     |
| TEA     | 2019 | 1  | 2   | 3   | 4   | 5   | -39   | -28  | -17   | -6    | -2    |
| TEA     | 2020 | 16 | 17  | 18  | 19  | 20  | 15    | 15   | 15    | 15    | 15    |
| COFFEE  | 2018 | 70 | 100 | 200 | 300 | 400 | 70    | 100  | 200   | 300   | 400   |
| COFFEE  | 2019 | 6  | 7   | 8   | 9   | 10  | -64   | -93  | -192  | -291  | -390  |
| COFFEE  | 2020 | 21 | 22  | 23  | 24  | 25  | 15    | 15   | 15    | 15    | 15    |
| TOFFEE  | 2018 | 30 | 7   | 400 | 34  | 90  | 30    | 7    | 400   | 34    | 90    |
| TOFFEE  | 2019 | 11 | 12  | 13  | 14  | 15  | -19   | 5    | -387  | -20   | -75   |
| TOFFEE  | 2020 | 26 | 27  | 28  | 29  | 30  | 15    | 15   | 15    | 15    | 15    |
+---------+------+----+-----+-----+-----+-----+-------+------+-------+-------+-------+

Best Answer

Because you only provided pictures i populated the tables with different data but this should alse apply to your case. Is this what you are looking for?

EDIT: I changed the query to be dynamic. This should do the trick. Of course, you have to change table name #tbl to your table name.

/*Creating a table and populating it*/
CREATE TABLE #tbl (product nvarchar(50), region nvarchar(10), [year] int, sales int);
DECLARE @sql nvarchar(max);
DECLARE @columnname nvarchar(max);

INSERT INTO #tbl(product, region, [year], sales)
SELECT  'TEA',  'D1',   2018,   40
UNION ALL SELECT    'TEA',  'D2',   2018,   30
UNION ALL SELECT    'TEA',  'D3',   2018,   20
UNION ALL SELECT    'TEA',  'D4',   2018,   10
UNION ALL SELECT    'TEA',  'D5',   2018,   7
UNION ALL SELECT    'COFFEE',   'D1',   2018,   70
UNION ALL SELECT    'COFFEE',   'D2',   2018,   100
UNION ALL SELECT    'COFFEE',   'D3',   2018,   200
UNION ALL SELECT    'COFFEE',   'D4',   2018,   300
UNION ALL SELECT    'COFFEE',   'D5',   2018,   400
UNION ALL SELECT    'TEA',  'D1',   2019,   1
UNION ALL SELECT    'TEA',  'D2',   2019,   2
UNION ALL SELECT    'TEA',  'D3',   2019,   3
UNION ALL SELECT    'TEA',  'D4',   2019,   4
UNION ALL SELECT    'TEA',  'D5',   2019,   5
UNION ALL SELECT    'COFFEE',   'D1',   2019,   6
UNION ALL SELECT    'COFFEE',   'D2',   2019,   7
UNION ALL SELECT    'COFFEE',   'D3',   2019,   8
UNION ALL SELECT    'COFFEE',   'D4',   2019,   9
UNION ALL SELECT    'COFFEE',   'D5',   2019,   10
UNION ALL SELECT    'TEA',  'D1',   2020,   13
UNION ALL SELECT    'TEA',  'D2',   2020,   26
UNION ALL SELECT    'TEA',  'D3',   2020,   39
UNION ALL SELECT    'TEA',  'D4',   2020,   52
UNION ALL SELECT    'TEA',  'D5',   2020,   65
UNION ALL SELECT    'COFFEE',   'D1',   2020,   78
UNION ALL SELECT    'COFFEE',   'D2',   2020,   91
UNION ALL SELECT    'COFFEE',   'D3',   2020,   104
UNION ALL SELECT    'COFFEE',   'D4',   2020,   117
UNION ALL SELECT    'COFFEE',   'D5',   2020,   130
;

SELECT @columnname=COALESCE(@columnname+ ',', '') + QUOTENAME(CAST(q.region AS nvarchar(20)),'[]')
FROM
(SELECT DISTINCT t.region
FROM #tbl t 
UNION ALL 
SELECT DISTINCT t.region+'D' AS region
FROM #tbl t) AS q



/*for your case, just replace the table name #tbl with the name of your table*/

SET @sql='
SELECT product, [year], '+@columnname+'
FROM
(SELECT product, 
CASE WHEN salee=''diff'' THEN region+''D'' ELSE region
END AS sales, [year], sale
FROM
(SELECT t.product, t.region, t.[year], t.sales, ISNULL((t.sales - LAG(t.sales) OVER(PARTITION BY t.product, t.region order by t.[year] asc)),t.sales) AS diff
FROM #tbl t) AS a
UNPIVOT
(sale for salee IN (sales, diff)) AS up) AS q
PIVOT 
(MAX(sale)
FOR sales IN ('+@columnname+')) AS p
ORDER BY product'

EXECUTE sp_executesql @sql

Output:

+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| product | year | D1 | D2  | D3  | D4  | D5  | D1D | D2D | D3D  | D4D  | D5D  |
+=========+======+====+=====+=====+=====+=====+=====+=====+======+======+======+
| COFFEE  | 2018 | 70 | 100 | 200 | 300 | 400 | 70  | 100 | 200  | 300  | 400  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2019 | 6  | 7   | 8   | 9   | 10  | -64 | -93 | -192 | -291 | -390 |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2020 | 78 | 91  | 104 | 117 | 130 | 72  | 84  | 96   | 108  | 120  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2018 | 40 | 30  | 20  | 10  | 7   | 40  | 30  | 20   | 10   | 7    |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2019 | 1  | 2   | 3   | 4   | 5   | -39 | -28 | -17  | -6   | -2   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2020 | 13 | 26  | 39  | 52  | 65  | 12  | 24  | 36   | 48   | 60   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+