question urgent please!!!! - 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)

question urgent please!!!!

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-02-27 : 17:59:18

I have a table with the following values:

col1 col2
emp1 aa
emp1 bb
emp1 cc
emp1 dd
emp2 qq
emp2 ww
emp2 ee

I am trying to create third column by concatenating values of col2 with a comma as follows:

col1 col2 col3
emp1 aa aa,bb,cc,dd
emp1 bb aa,bb,cc,dd
emp1 cc aa,bb,cc,dd
emp1 dd aa,bb,cc,dd
emp2 qq qq,ww,ee
emp2 ww qq,ww,ee
emp2 ee qq,ww,ee

please advice how I can have col3 displayed in the final output data. Thx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 18:20:39

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

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-27 : 23:45:28

select col1 ,col2 ,stuff(( select ','+col2 from urtable where col1 = t.col1 for xml path('')),1,1,'') as col3 from urtable t

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-02 : 01:24:42

Are you sure you want this way
col1 col2 col3
emp1 aa aa,bb,cc,dd
emp1 bb aa,bb,cc,dd
emp1 cc aa,bb,cc,dd
emp1 dd aa,bb,cc,dd
emp2 qq qq,ww,ee
emp2 ww qq,ww,ee
emp2 ee qq,ww,ee

I guess this one makes more sense
Col1 AllCol2
emp1 aa, bb, cc, dd
emp2 qq, ww, ee

Try


SELECT Col1,  AllCol2 = REPLACE((SELECT col2 as 'data()'
								FROM csv i 
								WHERE I.COL1 = csv.col1
								FOR XML PATH('')),' ',', ')
FROM csv 
GROUP BY Col1

http://mangalpardeshi.blogspot.com/2009/02/create-comma-delimited-list-from-column_888.html

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources