PIVOT into SQL Server

Question

I have a table with columns:

Anno, Description, Number
________________________
2011    Example1     12
2013    Example2      9
2011    Example4      8

......

The value into column Description is dynamic

I have to produce a table like:

Anno Example1 Example2 Example3 Example4 ........
--------------------------------------------------    
2011   12         0       0        8     ........
2012    0        23      24       36     ........
......

I have created a view like this:

Declare @cols AS NVARCHAR(Max),
        @query AS NVARCHAR(Max)

Select @cols = STUFF(( Select ','+QUOTENAME(Description)
                       FROM mytable
                       GROUP BY Description
                       Order by Description
                       FOR XML PATH ('',TYPE
                       ).value('.','NVARCHAR(Max)'),1,1,'')

SET @query= 'Select Anno, '+cols+' from
             (Select Anno, Description, Number
              FROM mytable ) as x
              PIVOT
              (
                SUM(Number)
                FOR Description in ('+cols+')
               ) pvt'

EXECUTE(@query)

It returns an error:

Incorrect syntax near ','

Answer 1

Couple of minor syntax errors. First you are missing a close paren in the FOR XML PATH.

FOR XML PATH ('',TYPE

becomes

FOR XML PATH (''),TYPE

And second you are storing the column list in a variable and not referencing it as one in your dynamic SQL. This works correctly.

Declare @cols AS NVARCHAR(Max),
        @query AS NVARCHAR(Max)

Select @cols = STUFF(( Select ','+QUOTENAME(Description)
                       FROM mytable
                       GROUP BY Description
                       Order by Description
                       FOR XML PATH (''),TYPE
                       ).value('.','NVARCHAR(Max)'),1,1,'')

SET @query= 'Select Anno, '+@cols+', Total from
             (Select Anno, Description, Number, 
                SUM(Number) OVER (PARTITION BY Anno) AS Total
              FROM mytable ) as x
              PIVOT
              (
                SUM(Number)
                FOR Description in ('+@cols+')
               ) pvt'

SQL FIDDLE

Answer 2

Try this one -

DECLARE @query NVARCHAR(MAX)

SELECT @query = '
    SELECT Anno, ' + cols + ' 
    FROM (
        SELECT Anno, Description, Number
        FROM dbo.mytable 
    ) x
    PIVOT
    (
    SUM(Number) FOR [Description] IN (' + cols + ')
    ) pvt'
FROM (
    SELECT cols = STUFF((
        SELECT ', [' + [Description] + ']'
        FROM dbo.mytable d
        GROUP BY [Description]
        ORDER BY [Description]
        FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) t

EXEC sys.sp_executesql @query