Multiple columns need to be separate rows

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)

Multiple columns need to be separate rows

Author

Topic

cosensp
Starting Member

2 Posts

Posted - 2007-04-25 : 10:16:25

Hi guys, wondered if anyone could help me out here -
I have a table, with columns:
id - name - data1 - data2 - data3 - data4
1 - paul - sswn - sswb - sswc - sswd
2 - peter - sswn - sswh - sswt - sswj
3 - simon - sswn - sswh - sswc - sswf

I need to create a SQL Script to convert this so that, data1, data2, data3 are on seperate rows, e.g.
id - name - data
1 - paul - sswn
1 - paul - sswb
1 - paul - sswc
1 - paul - sswd
2 - peter - sswn
2 - peter - sswh ...

Thanks
Phil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 10:21:28

[code]
select id, name, data1 as data
from table
union all
select id, name, data2 as data
from table
union all
select id, name, data3 as data
from table
. . . .
[/code]

cosensp
Starting Member

2 Posts

Posted - 2007-04-25 : 10:31:42

Thanks alot - thats what I was after. Thought it was something quite simple.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources