SQL Server 2012 Dynamic Rotation
I am trying to get my first dynamic rod running in SQL Server 2012.
My table #temp
that I am using for dynamic rotation looks like this.
YearMonth Agreement nr Discount
------------------------------------
201303 123 1
201303 12 0
201304 1 0
I run this code and it doesn't 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?
+3
source to share
3 answers
The reason for the error is that you are missing a parenthesis before you generate the Pivot. What's more, but your pivot was pretty ineffective.
You have to choose what you want for the original table in the pivot, otherwise it might work for a long time and create many zero-returning rows.
The following is a fix 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;
+1
source to share