dynamic sql pivot in sql server

Question

Running this query in sql server 2008 now, but soon plan to move it in creating a report in sql reporting services:

SELECT * from ( SELECT Amount, Year, column1, column2,column3 from BUYSCTE ) BUY

My table results running above query without the pivot is this

Column1 | Column2 | Column3| FYYear| Amount|

 1           cat     dog      2011   50 
 1           cat     dog      2012   75
 1           cat     dog      2013   65
 2          fish     snake    2011   23
 2          fish     snake    2012   39
 2          fish     snake    2013   59
 .. 
 ..  
 ..                           2016

so basically I want the results to end up like this:

Column1 | Column2 | Column3| 2011| 2012 | 2013 
 1           cat     dog      50    75     65
 2          fish     snake    23    39     59

so the query I came up with to make this happen is I first created a table variable and made it a dynamic variable and did a pivot like this,

declare @Year nvarchar(Max)
set @Year = STUFF(
(SELECT ', ' + quotename(FYYear)
from BUYSCTE Group By FYYear order by
FYYear For XML PATH(''))
, 1, 2, '');

The above populates the @Year value with: [2011], [2012], [2013], [2014], [2015], [2016]

I know that because I change the above select to SELECT @Year just to check if the value is set correctly

from there I change the above select after the table variable to

SELECT * from ( SELECT Amount, FYYear, column1, column2,column3 from BUYSCTE ) BUY 
PIVOT( SUM(Amount) FOR FYYear in ([@Year]) ) pvt

but my results end up being this

 Column1 | Column2 | Column3| @Year|
 1           cat     dog      null   
 2          fish     snake    null

What am I doing wrong? it seems like something small I am missing that I cannot see myself. even if I change the aggregate function from SUM to COUNT it gives 0 for the @Year column values instead of null

Have you tried searching this site? Have you had a look at questions suggested in the Related section here? — Andriy M
– Andriy M, Commented Nov 30, 2011 at 14:19

Lamak · Accepted Answer · 2011-11-30 14:24:05Z

2

You are close, but for this to work you have to construct your PIVOT using dynamic SQL and then execute it. So, after you populate your variable @Year, you need to do something like this:

DECLARE @Query VARCHAR(MAX)

SET @Query = '
SELECT * from ( SELECT Amount, FYYear, column1, column2,column3 from BUYSCTE ) BUY 
PIVOT( SUM(Amount) FOR FYYear in ('+ @Year + ') ) pvt'

EXEC(@Query)

Though before doing this, you should take a look at this link.

answered Nov 30, 2011 at 14:24

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lamak Over a year ago

@Jake - It will if the number of columns doesn't change, but I strongly recommend you to not use it for that purpose. Reporting services has a Matrix tool, that is exactly for this kind of needs, you should pass a dataset directly from your BYUSCTE table and use a Matrix to pivot your years

Collectives™ on Stack Overflow

dynamic sql pivot in sql server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related