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 ','

Is the error occurring on the `EXECUTE` line? Assuming it is, when doing dynamic query generation like this, it's often useful to `PRINT` the query before executing it. What does `@query` contain just before it is executed? — Daniel Renshaw, May 30 '13 at 15:41
But I wish I had back only a table to display via programming. Tanks for all — Antony, May 30 '13 at 15:54
If you add a `PRINT` statement and then execute it all in Management Studio, you should see the query printed to the output. — Daniel Renshaw, May 30 '13 at 15:56

score 2 · Answer 1 · answered May 31 '13 at 04:30

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

Also,How can I add a column ' Totals ' that is the sum of the dynamic fields?? — Antony, Jun 01 '13 at 10:49

Kenneth Fisher · Accepted Answer · 2013-06-01T12:34:19.000

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

edited Jun 01 '13 at 12:34

answered May 30 '13 at 15:55

Kenneth Fisher

3,692
19
21

+1. Was so close... Got the mistake by comparing **[Query from here](http://stackoverflow.com/questions/15042663/dynamic-sql-server-pivot-table)** But you were the first one. – Prahalad Gaggar May 30 '13 at 15:58
Tanks it's ok But when you save the View gives me the error: incorrect syntax near ' DECLARE – Antony May 30 '13 at 16:33
You can't do declares in a view. Or multiple queries for that matter. If you want it to be a view you will have to lay it out completely differently. – Kenneth Fisher May 30 '13 at 16:39
Also,How can I add a column ' Totals ' that is the sum of the dynamic fields?? – Antony Jun 01 '13 at 10:50

PIVOT into SQL Server

2 Answers2