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?

Answer 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;