Dynamic Pivot Table by Month

I’m trying to create a dynamic pivot table in SQL that will report based on month and year. I did a bunch of research and was able to come up with the below query:

declare @dynamic nvarchar(max),
@column nvarchar(max);

set @column = N'';
select @column += N'' + datename(month,incurdate) +' '+ datename(year,incurdate) + ',' 
from (select distinct a.incurdate from artable a) as Transpose

select @column = substring(@column,0,len(@column))

set @dynamic = 'select * from
 (
select month, incurdate, dolamount
from artable join dolentry on month = period
) b
pivot(sum(dolamount) for incurdate in (' + @column + ')) as PivotTable'

execute sp_executesql @dynamic

I am able to print the @column variable successfully, but the problems happen when I try to set it in the @dynamic variable. The error message is ‘Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ‘1990’.‘ 1990 is the first year of the first pivoted column. Any help or tips are appreciated. Thanks!

Answer

You need to use QUOTENAME in the following code:

select @column += N'' + QUOTENAME(datename(month,incurdate) +' '+ datename(year,incurdate)) + ',' 
from (select distinct a.incurdate from artable a) as Transpose

in order to get output like this:

[col01], [col02], [col03], ... , [col04]

As you can see from the docs, the PIVOT syntax requires the pivoting columns to be wrapped in square brackets:

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;