Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how tp PIVOT this table? 1

Status
Not open for further replies.

yehong

Programmer
Sep 22, 2003
291
US
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
 
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
 
how about this?

Code:
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
 
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?
 
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..

Code:
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top