Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL - Follow up!

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things - see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday's post I showed a query that could create a year-to-year Inventory Margin report using T-SQL's PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt

So naturally, Corey's question was, "What if I want to add the pivoted columns list dynamically, instead of hardcoding them?". Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT  @listCol = STUFF(
 ( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FROM SOP30200
   ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
   FOR XML PATH('')
 ), 
 1, 
 2, 
 '') + ']'

SET @sqlstmt = 
N';WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],' + @listCol + N'
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN (' + @listCol + N')
 ) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations...

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG                          Green Phone                                                                                           NULL NULL 307.05000 40.05000 NULL
3-B3813A                        Keyboard                                                                                              NULL NULL NULL 40.00000 NULL
3-C2924A                        SCSI Cable, 2.5m. 68-pin HI-Density                                                                   NULL NULL NULL 148.50000 NULL
3-C2924A                        T0101 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 84.38000
3-C2924A                        T0102 - SCSI Cable, 2.5m. 68-pin HI-Density                                                           NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do's and Don'ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/