change rows 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 2005 Forums

Transact-SQL (2005)

change rows into columns

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-03-30 : 11:45:55

Hi all,
How can i change the below input

1 Name1 100 200 300
2 Name2 400 500 600
3 Name3 700 800 900

into

1 Name1 100
1 Name1 200
1 Name1 300
2 Name2 400
2 Name2 500
2 Name2 600
3 Name3 700
4 Name3 800
5 Name3 900

-Thanks N Regards,
Chinna.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-30 : 11:52:21

Use PIVOT or UNPIVOT, search the forums for more as there are loads of examples around.

buzzi
Starting Member

48 Posts

Posted - 2009-03-30 : 19:19:41

if your sample input is mentioned as columns; then you can use union all to get the out put mentioned

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-30 : 22:53:31

try this one


declare @tab table(id int,nameval varchar(32),value1 int,value2 int,value3 int)
insert into @tab select 1, 'Name1', 100, 200, 300
insert into @tab select 2, 'Name2', 400, 500, 600
insert into @tab select 3, 'Name3', 700, 800, 900

SELECT id,nameval,namevalue, val
FROM @tab
UNPIVOT(val FOR namevalue IN (value1,value2,value3))AS pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources