Help needed on a select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed on a select statement

Author

Topic

rhubman16
Starting Member

3 Posts

Posted - 2009-03-04 : 11:47:56

I have a table that has the following data in it:

part mth1 mth1qty mth2 mth2qty mth3 mth3qty qtr

123 jan 5 feb 7 mar 0 1
123 apr 2 may 5 jun 9 2
123 jul 0 aug 1 sep 2 3
123 oct 1 nov 1 dec 1 4

and I want the output record to look like

part months as columns
123 5 7 0 2 5 9 0 1 2 1 1 1

Can someone please help me with how I can do this?

thank you
Rob

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 11:55:00

quote:
Originally posted by rhubman16

I have a table that has the following data in it:

part mth1 mth1qty mth2 mth2qty mth3 mth3qty qtr

123 jan 5 feb 7 mar 0 1
123 apr 2 may 5 jun 9 2
123 jul 0 aug 1 sep 2 3
123 oct 1 nov 1 dec 1 4

and I want the output record to look like

part months as columns
123 5 7 0 2 5 9 0 1 2 1 1 1

Can someone please help me with how I can do this?

thank you
Rob

Search for PIVOT or Cross-tab queries over here.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 12:03:29

Try this...

declare @t table (col1 int, mon1 char(3), mon1q int, mon2 char(3), mon2q int, mon3 char(3), mon3q int)
insert @t
select 123,'jan',5,'feb',7,'mar',0 union all
select 123,'apr',2,'may',5,'jun',9 union all
select 123,'jul',0,'aug',1,'sep',2 union all
select 123,'oct',1,'nov',1,'dec',1

select col1,
max(case mon1 when 'jan' then mon1q else 0 end),
max(case mon2 when 'feb' then mon2q else 0 end),
max(case mon3 when 'mar' then mon3q else 0 end),
max(case mon1 when 'apr' then mon1q else 0 end),
max(case mon2 when 'may' then mon2q else 0 end),
max(case mon3 when 'jun' then mon3q else 0 end),
max(case mon1 when 'jul' then mon1q else 0 end),
max(case mon2 when 'aug' then mon2q else 0 end),
max(case mon3 when 'sep' then mon3q else 0 end),
max(case mon1 when 'oct' then mon1q else 0 end),
max(case mon2 when 'nov' then mon2q else 0 end),
max(case mon3 when 'dec' then mon3q else 0 end)
from @t group by col1

Result
123 5 7 0 2 5 9 0 1 2 1 1 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 12:53:58

[code]
SELECT part,
[jan],[feb],[mar],[apr],[may],
[jun],[jul],[aug],[sep],[oct],[nov],[dec]
FROM
(SELECT part,mth1 as mth,mth1qty as qty
FROM Table
UNION ALL
SELECT part,mth2,mth2qty
FROM Table
UNION ALL
SELECT part,mth3,mth3qty
FROM Table
)t
PIVOT (MAX(qty) FOR mth IN ([jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec]))p
[/code]

rhubman16
Starting Member

3 Posts

Posted - 2009-03-04 : 13:21:00

visakh16,

When I try that, I only get data in the may category for the parts, all others are NULL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:24:45