how tp PIVOT this table? 1

[yehong]

Hi Guys, I am on a tight deadline and would really appreciate if you guys can provide some input. I am using SQL Server 2005.
I have following table:
Year Dept Month Tkt_Cnt
2008 R&D 02 2
2008 R&D 03 5
2008 R&D 01 6
2007 R&D 06 3
2007 HR 05 8
2007 HR 06 10

The result should look like:
Year Dept 01 02 03 04 05 06 07 08 09 10 11 12
2008 R&D 6 0 5 0 0 0 0 0 0 0 0 0
2007 R&D 0 0 0 0 0 3 0 0 0 0 0 0
2007 HR 0 0 0 0 8 1 0 0 0 0 0 0

 

[SQLSister]

Suggest you look up the pivot keyword in BOoks on line since you are using 2005

"NOTHING is more important in a database than integrity." ESquared

 

[chamilz]

how about this?

create table #t
(t_year char(4),
 t_dept varchar(3),
 t_month varchar(2),
 t_tkt_cnt smallint)

insert #t
select '2008','R&D','02',2 union
select '2008','R&D','03',5 union
select '2008','R&D','01',6 union
select '2007','R&D','06',3 union 
select '2007','HR','05',8 union
select '2007','HR','06',10


select  t_year,
	t_dept,
	isnull(sum([01]),0) as month_01, 
	isnull(sum([02]),0) as month_02,
	isnull(sum([03]),0) as month_3,
	isnull(sum([04]),0) as month_4,
	isnull(sum([05]),0) as month_5,
	isnull(sum([06]),0) as month_6,
	isnull(sum([07]),0) as month_7,
	isnull(sum([08]),0) as month_8,
	isnull(sum([09]),0) as month_9,
	isnull(sum([10]),0) as month_10,
	isnull(sum([11]),0) as month_11,
	isnull(sum([12]),0) as month_12
from #t
PIVOT
(sum(t_tkt_cnt)
 for t_month in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) a
group by t_year,t_dept
order by t_year desc

 

[yehong]

Thanks chamilz. It works perfectly. I have another question. Would it be possible to change the order of months in 'select' statement based upon the order of months from another table? Like I have another table that always has current month -12 months i.e,
month year
03 2008
02 2008
01 2008
12 2007
11 2007
...
03 2007
So, I want my select statement's first month to be March2007 and onwards. Any ideas?

 

[chamilz]

You could use dynamic sql to retrieve the data based upon the order of months. I have an idea however, there may be better solutions for this.

1.load the Pivot result into a temp table.
2.Assign result table's month columns to variables based on your MONTH table.
3. create your dynamic sql

Somehthing like this..

declare @sql varchar(1000)
declare @m1 varchar(10), @m2 varchar(10)


if (select t_month from #month where MONTH_TABLE = 3)= 3
set @m1= 'month_3'

if (select t_month from #month where MONTH_TABLE = 4)= 4
set @m2= 'month_4'


set @sql = 'select t_year, t_dept,'+ @m1+','+@m2+' from #temp'
exec (@sql)