Comma Separated Values in a Column

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)

Comma Separated Values in a Column

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-10-09 : 02:02:17

One of my table contains data

RoomNo Equipment
====== =========
1 CPU
1 MONITOR
1 PRINTER
2 FAX
2 PROJECTOR

I need a query which can give me results in following format

RoomNo Equipments
====== ==========================
1 CPU,MONITOR,PRINTER
2 FAX,PROJECTOR

(I DON'T WANT TO USE CURSOR FOR THIS PURPOSE)

Thanx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 02:10:32

[code]SELECT t.RoomNo,LEFT(el.EquipList,LEN(el.EquipList)-1)
FROM (SELECT DISTINCT RoomNo FROM yourtable) t
CROSS APPLY (SELECT Equipment + ',' AS [text()]
FROM YourTable
WHERE RoomNo=t.RoomNo
FOR XML PATH(''))el(EquipList)[/code]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-10-09 : 02:27:18

thanx a lot Mr. Genius

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 02:34:02

quote:
Originally posted by lionofdezert

thanx a lot Mr. Genius

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 03:03:51

Visakh, may I ask why you have "AS [text()]" in the CROSS APPLY derived table, when you also name the derived table and column with "AS el(EquipList)"?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources