Creating Dynamic Pivot Table Column Names

Question

I have created a pivot table with hard coded column names. The pivot table simply keeps a rolling sum of sales by qty (current month + 11 months back).

It was my first time using the PIVOT function properly and the code works fine.

SELECT
    Item_Code_Desc,
    ISNULL([Current],0)     AS [Current],
    ISNULL([1],0)           AS [1],
    ISNULL([2],0)           AS [2],
    ISNULL([3],0)           AS [3],
    ISNULL([4],0)           AS [4],
    ISNULL([5],0)           AS [5],
    ISNULL([6],0)           AS [6],
    ISNULL([7],0)           AS [7],
    ISNULL([8],0)           AS [8],
    ISNULL([9],0)           AS [9],
    ISNULL([10],0)          AS [10],
    ISNULL([11],0)          AS [11]
FROM
    (SELECT
         CONCAT(ST.Code,' - ', ST.Description_1) AS Item_Code_Desc,
         STT.ActualQuantity AS Qty,
         CASE
            WHEN MONTH(STT.TxDate) = MONTH(GETDATE()) THEN 'Current'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -1, GETDATE())) THEN '1'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -2, GETDATE())) THEN '2'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -3, GETDATE())) THEN '3'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -4, GETDATE())) THEN '4'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -5, GETDATE())) THEN  '5'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -6, GETDATE())) THEN '6'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -7, GETDATE())) THEN '7'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -8, GETDATE())) THEN '8'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -9, GETDATE())) THEN   '9'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -10, GETDATE())) THEN '10'
            WHEN MONTH(STT.TxDate) = MONTH(DATEADD(MONTH, -11, GETDATE())) THEN '11'
            ELSE '0'
         END AS [Period]
     FROM 
         _bvSTTransactionsFull AS STT
     INNER JOIN 
         StkItem AS ST ON STT.AccountLink = ST.StockLink
     WHERE 
         STT.TxDate >= DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
         AND STT.Module = 'AR') AS P
PIVOT  
    (SUM(P.Qty)
        FOR P.Period IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[Current])
    ) AS PVT

To make the output more dynamic, I wanted to rather show the month and year as the field heading, rather than just the 1,2,3 etc.

To attempt this, I first took out the original CASE, and used the CONCAT function to get the desired result

CONCAT(DATENAME(MONTH,STT.TxDate),' ',YEAR(STT.TxDate))                     AS [Period],

Now that the sub-query was showing the transaction date in the format "mmmm yyyy", I wanted the table to pivot on this. I did start wondering how it was going to do this as I was reaching the end, as the column names are not hard coded anymore.

After trying

 PIVOT (
            SUM(P.Qty)
        FOR  CONCAT(DATENAME(MONTH,P.TxDate),' ',YEAR(P.TxDate)) 
            ) AS PVT

And many other variations I did do some research, and I see this process is slightly more complex than I thought.

I haven't used the STUFF and FOR XML PATH before. I have attempted to convert the above into the examples I have found on the net. But I'm trying in vein as I don't understand the core logic of what I am trying to do.

Please could I have assistance with not only how to get the pivot dynamic, but perhaps some notes to further understand what is happening. Really appreciate some help on this!

After Attempting

This is my attempt to get it right:

DECLARE
@Cols NVARCHAR(MAX),
@Query NVARCHAR(MAX),
@Module NVARCHAR = 'AR'

 SELECT 
@Cols = STUFF((SELECT DISTINCT ',' + 'CONCAT(DATENAME(MONTH, STT.TxDate),,YEAR(STT.TxDate))' + QUOTENAME(NAME)
                FROM _bvSTTransactionsFull AS STT
                WHERE STT.Module = 'AR'
                FOR XML PATH (''), TYPE).VALUE('.','NVARCHAR(MAX)'),1,1,'')
 SELECT
@Query = '
SELECT
Item_Code,
Item_Desc,
' +''''+ @Cols + '''''
FROM
 (SELECT
     ST.Code                                                                            AS Item_Code, 
     ST.Description_1                                                                   AS Item_Desc,
     STT.ActualQuantity                                                                 AS Qty,
     CONCAT(DATENAME(MONTH, STT.TxDate),,YEAR(STT.TxDate))                              AS [Period]
 FROM 
     _bvSTTransactionsFull AS STT
 INNER JOIN 
     StkItem AS ST ON STT.AccountLink = ST.StockLink
 WHERE 
     STT.TxDate >= DATEADD(MONTH, -13, DATEADD(MONTH, DATEDIFF(MONTH, 0, 
 GETDATE()), 0))
     AND STT.Module = '+ @Module +') AS P
 PIVOT  
(SUM(P.Qty)
    FOR P.Period IN ('+@cols+')
) AS PVT '
PRINT @Query
EXEC (@Query)

But I'm getting the following error:

Msg 207, Level 16, State 1, Line 7 Invalid column name 'NAME'.

Where am I off here?

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ — Aaron Bertrand, Aug 16 '19 at 14:50
Cross tab might also be far easier than the `PIVOT` operator. [Cross Tabs and Pivots](https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1) — Thom A, Aug 16 '19 at 14:51
@AaronBertrand I have added my attempt. Please assist me with where I am going wrong? — Gray Meiring, Aug 16 '19 at 19:43

score 0 · Answer 1 · answered Aug 16 '19 at 21:01

Solved :)

DECLARE
@Cols1  VARCHAR(MAX),
@Cols2  VARCHAR(MAX),
@Query  VARCHAR(MAX),
@Period VARCHAR(MAX) = -12 ; --/ Select number of months to view back on (excluding current month) \--

SELECT 
@Cols1 = STUFF((SELECT DISTINCT ',' +'ISNULL('+ QUOTENAME(CONCAT(DATENAME(MONTH, S.TxDate),' ',YEAR(S.TxDate)))+',0) AS' + QUOTENAME(CONCAT(DATENAME(MONTH, S.TxDate),' ',YEAR(S.TxDate))) 
                FROM _bvSTTransactionsFull AS S
                WHERE S.Module = 'AR' AND S.TxDate > = DATEADD(MONTH, -13, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
                FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')                     --/ allows the first SELECT fields to have the ISNULL function and Alias \--
SELECT
@Cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT(DATENAME(MONTH, S.TxDate),' ',YEAR(S.TxDate)))
                FROM _bvSTTransactionsFull AS S
                WHERE S.Module = 'AR' AND S.TxDate > = DATEADD(MONTH, -13, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
                FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')  --/ becasue of the need for ISNULL above, second @Cols needed for the Pivot (pivot cannot have ISNULL in it) \--

SELECT
   @Query =
'SELECT
Item_Code_Desc,
'+@Cols1+'
FROM
(SELECT
     CONCAT(ST.Code,'' - '', ST.Description_1)                                  AS Item_Code_Desc,
     STT.ActualQuantity                                                         AS Qty,
     CONCAT(DATENAME(MONTH, STT.TxDate),'' '',YEAR(STT.TxDate))                 AS [Period]
 FROM 
     _bvSTTransactionsFull AS STT
 INNER JOIN 
     StkItem AS ST ON STT.AccountLink = ST.StockLink
 WHERE 
     STT.TxDate >= DATEADD(MONTH, '+@Period+', DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
     AND STT.Module = ''AR'') AS P
PIVOT  
(SUM(P.Qty)
    FOR P.Period IN ('+@cols2+')
) AS PVT '

PRINT @Query
EXEC (@Query)

Attie Wagner · Accepted Answer · 2019-08-17T07:56:11.507

Your result is correct above however, you will not have your columns sorted by the correct date...

The below would cater for that:

DECLARE
@Cols1  VARCHAR(MAX),
@Cols2  VARCHAR(MAX),
@Query  VARCHAR(MAX),
@Period VARCHAR(MAX) = -13 ; --/ Select number of months to view back on (excluding current month) \--


declare @tmptbl table (PeriodDate datetime, col1 varchar(100), col2 varchar(100))
insert into @tmptbl (PeriodDate, col1, col2)

SELECT DISTINCT dPeriodDate,'ISNULL('+ QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate)))+',0) AS' + QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate))) col1
,   QUOTENAME(CONCAT(DATENAME(MONTH, dPeriodDate),' ',YEAR(dPeriodDate))) col2
                FROM _bvSTTransactionsFull AS S join _etblPeriod p on EOMONTH(s.TxDate) = p.dPeriodDate
                WHERE S.Module = 'AR' AND S.TxDate > = DATEADD(MONTH, -13, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

SELECT 
@Cols1 = STUFF((SELECT ',' + col1 
                FROM @tmptbl order by PeriodDate
                FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')                     --/ allows the first SELECT fields to have the ISNULL function and Alias \--
SELECT
@Cols2 = STUFF((SELECT ',' + col2
                FROM @tmptbl order by PeriodDate
                FOR XML PATH (''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')  --/ becasue of the need for ISNULL above, second @Cols needed for the Pivot (pivot cannot have ISNULL in it) \--

SELECT
   @Query =
'SELECT
Item_Code_Desc,
'+@Cols1+'
FROM
(SELECT
     CONCAT(ST.Code,'' - '', ST.Description_1)                                  AS Item_Code_Desc,
     STT.ActualQuantity                                                         AS Qty,
     CONCAT(DATENAME(MONTH, STT.TxDate),'' '',YEAR(STT.TxDate))                 AS [Period]
 FROM 
     _bvSTTransactionsFull AS STT
 INNER JOIN 
     StkItem AS ST ON STT.AccountLink = ST.StockLink
 WHERE 
     STT.TxDate >= DATEADD(MONTH, '+@Period+', DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
     AND STT.Module = ''AR'') AS P
PIVOT  
(SUM(P.Qty)
    FOR P.Period IN ('+@cols2+')
) AS PVT '

PRINT @Query
EXEC (@Query)

I left the changes I made in small caps for you to see the changes...

Found a flaw, column July has no quantities, but that's due to your period specification - it should be -13 like your columns... edited it in my answer... — Attie Wagner, Aug 17 '19 at 07:55

Creating Dynamic Pivot Table Column Names

2 Answers2