Multiple row value in one record

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

General SQL Server Forums

New to SQL Server Programming

Multiple row value in one record

Author

Topic

awalsql
Starting Member

11 Posts

Posted - 2008-06-11 : 10:25:26

SOURCE TABLE
ID SEQ COM VARCHAR(10)
1 1 I
1 2 am
1 3 doctor
2 1 I
2 2 am nurse
DESTINATION TABLE --Each comm field is varchar(20)
ID_COM1___COM2______COM3
1__I______am_________doctor
2__I______am nurse____NULL

Please Help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 10:33:41

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:46:04

If you're sure that you'll have maximum of 3 records per each ID then you can do like this also

SELECT ID,
MAX(CASE WHEN SEQ=1 THEN COM ELSE NULL END) AS COM1,
MAX(CASE WHEN SEQ=2 THEN COM ELSE NULL END) AS COM2,
MAX(CASE WHEN SEQ=3 THEN COM ELSE NULL END) AS COM3
FROM SourceTable
GROUP BY ID

awalsql
Starting Member

11 Posts

Posted - 2008-06-11 : 10:46:57

Hi But I am using sql2000. Is there any easy way to do this in sql 2000.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:05:01

Did you follow visakh?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:05:59

Also refer http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail