convert row into columns - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

convert row into columns

Author

Topic

khana04
Starting Member

4 Posts

Posted - 2009-03-18 : 23:52:03

that how the data is
SKU PO# QTY Date
1 '640050193' 'X1234' 122 '03/13/2009'
2 '640050193' 'V3344' 244 '02/11/2009'
3 '640050193' 'A1233' 43 '02/9/2009'

I want to convert it into columns this way

SKU PO#1 PO#2 Po#3 QTY1 QTY2 QTY3
'640050193' 'X1234' 'V3344' 'A1233' '122' '244' '43'

max number of PO in my date range is 3 so total column will be 7 or less

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-19 : 00:18:00

Use dynamic CrossTab it will be easy for u

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-19 : 00:19:05

declare @tab table (SKU varchar(12), PO# varchar(12),QTY int, Date datetime)
insert into @tab select '640050193', 'X1234',122, '03/13/2009' union all select
'640050193', 'V3344', 244, '02/11/2009'union all select
'640050193', 'A1233', 43, '02/9/2009'

select sku,max(case when date ='03/13/2009' then po# end)as po#1,
max(case when date ='02/11/2009' then po# end)as po#2,
max(case when date ='02/9/2009' then po# end)as po#3,
max(case when date ='03/13/2009' then QTY end)as QTY1,
max(case when date ='02/11/2009' then QTY end)as QTY1,
max(case when date ='02/9/2009' then QTY end)as QTY1
from @tab
group by sku

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources