October 22, 2009 at 8:01 am
This is my query
select year, ID, val
from table1 t1
left join table2 t2 on t1.companyid = t2.companyid
the output of the query is :
year ID val
20087NULL
200970.00
201070.00
201170.00
20088NULL
200980.00
201080.00
201180.00
now i want to pivot this table to get the output as below
year 7 8
2008 null null
2009 0.00 0.00
2010 0.00 0.00
2011 0.00 0.00
Now there can be many numbers of companyid so the number of colums in the final pivot query will increase or decrease based on distinct Id numbers.
Can you please help me with this.
thanks
October 22, 2009 at 9:38 am
Sorry, I aint givin the complete solution. This I guess you would anyway have it. I am really interested to know how anyone will approach this other part of the problem (unknown number of pivot columns)
CREATE TABLE #T3(iYear int, ID int , Val float)
INSERT INTO #T3 VALUES (2008,7,NULL)
INSERT INTO #T3 VALUES (2009,7,0.0)
INSERT INTO #T3 VALUES (2010,7,0.0)
INSERT INTO #T3 VALUES (2011,7,0.0)
INSERT INTO #T3 VALUES (2008,8,NULL)
INSERT INTO #T3 VALUES (2009,8,0.0)
INSERT INTO #T3 VALUES (2010,8,0.0)
INSERT INTO #T3 VALUES (2011,8,0.0)
select
iYear, [7], [8]
from #t3 T
PIVOT (sum(Val) FOR ID IN ([7],[8])) PVT
---------------------------------------------------------------------------------
October 22, 2009 at 7:38 pm
You basically need to use dynamic SQL to generate the columns for the pivot. Jeff wrote an article on it here:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply