Dynamic pivoting SQL Server 2012

Question

I am making attempts to run my first dynamic pivot in SQL Server 2012.

My #temp table that I am using for the dynamic pivoting looks like this.

YearMonth   Agreement nr    Discount
------------------------------------
201303         123            1
201303          12            0
201304           1            0

I am running this code and it does not work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(YearMonth )
FROM (SELECT DISTINCT YearMonth FROM #FINAL) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT [Agreement nr],YearMonth , ' + @ColumnName + '
    FROM #FINAL
    PIVOT(
            COUNT(agreement nr) 
          FOR YearMonth IN (' + @ColumnName + ') AS PVTTable'
--Execute the Dynamic Pivot Query

EXECUTE  @DynamicPivotQuery;

The error message I am getting is

FOR YearMonth IN ([201403]) AS PVTTable' is not a valid identifier.

What am I missing here?

Christian Barron · Accepted Answer · 2015-07-16T08:35:56.430

1

The cause of the error is that you're missing a parenthesis before you alias the Pivot. More than this however your pivot was rather inefficient.

You should select what you need for the source table in your pivot otherwise it could run for a long time and produce a lot of rows with null returns.

The below is fixed and hopefully more efficient:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(YearMonth )
FROM (SELECT DISTINCT YearMonth FROM #FINAL) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + '
    FROM (Select [Agreement nr], YearMonth from #FINAL) src
    PIVOT(
            COUNT([Agreement nr]) 
          FOR YearMonth IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query

EXECUTE sp_executesql @DynamicPivotQuery;

edited Jul 16 '15 at 08:35

answered Jul 09 '15 at 13:36

Christian Barron

2,695
1
14
22

I tried the solution and it throws an error: COUNT(Agreement nr) FOR YearMonth IN ([201503],[201506],[201504],[201505])) AS PVTTable' is not a valid identifier. Could it have to do that a GROUP BY is needed since we are doing COUNT and not doing a GROUP BY containing the YearMonth? – user3197575 Jul 16 '15 at 06:22
Try put "Agreement nr" in square brackets too: [Agreement nr] – Christian Barron Jul 16 '15 at 06:37
Interesting. I put the brackets around it and it still throws the same error:Msg 203, Level 16, State 2, Line 20 The name 'SELECT [Agreement nr],YearMonth, [201503],[201506],[201504],[201505] FROM (Select [Agreement nr], YearMonth from #tester) src PIVOT( COUNT([Agreement nr]) FOR YearMonthIN ([201503],[201506],[201504],[201505])) AS PVTTable' is not a valid identifier. – user3197575 Jul 16 '15 at 06:49
Have you tried running the statement normally and hard coding in the @ColumnName values? – Christian Barron Jul 16 '15 at 08:17
Ok I figured out what was happening here. You need to sp_executesql to execute the statement. You also can't have Agreement nr or YearMonth in the select as you are making YearMonth into columns so you can't make it rows too and you're also populating the columns with the count of Agreement nr so that can't be rows either. Make sense? I've updated the code above to reflect this – Christian Barron Jul 16 '15 at 08:37
Thank you Christian. I am understand it better now. – user3197575 Jul 16 '15 at 09:26

score 0 · Answer 2 · edited Jul 09 '15 at 13:14

0

You are missing a parenthesis

SET @DynamicPivotQuery = 
  N'SELECT [Agreement nr],YearMonth , ' + @ColumnName + '
    FROM #FINAL
    PIVOT(
            COUNT([agreement nr]) 
          FOR YearMonth IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query

edited Jul 09 '15 at 13:14

Mike Chamberlain

39,692
27
110
158

answered Jul 09 '15 at 13:11

Madhivanan

13,470
1
24
29

score 0 · Answer 3 · answered Jul 09 '15 at 13:13

0

You forgot to close pivot.

PIVOT(
        COUNT(Kundavtalid) 
        FOR YearMonth IN (' + @ColumnName + ') 
     ) AS PVTTable' -- here you miss pathernesis

answered Jul 09 '15 at 13:13

Stanislovas Kalašnikovas

9,203
4
32
59

Dynamic pivoting SQL Server 2012

3 Answers3