How to get values in comma seperated

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

How to get values in comma seperated

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-10-25 : 08:08:55

Hello all,
i had a table with values like one to many relation

col1 Col2
1 A
1 A
1 B
2 A
2 B
3 A
3 B

but i need to get out put like

col1 col2
1 A,A,B
2 A,B
3 A,B

can u suggest me ????

P.V.P.MOhan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 08:11:26

On SQL 2005 and greater you can use XML PATH as shown below

SELECT
	a.col1,
	STUFF(b.col2s,1,1,'') AS Col2s
FROM
	(SELECT DISTINCT col1 FROM YourTable) a
	CROSS APPLY
	(
		SELECT ','+col2 FROM YourTable b
		WHERE a.col1 = b.col1
		FOR XML PATH('')
	)b(col2s);

Mike Jackson
Starting Member

37 Posts

Posted - 2012-10-25 : 08:11:27

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 08:11:52

see this

http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

select col1,
csvstr = stuff (
(
select ',' + col2
from @table t2 where t.col1 = t2.col1
for xml path('')
)
,1,1,'')
from (select distinct col1 from @table) t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 08:32:42

declare @TestTable Table(col1 int, Col2 varchar(3))
INsert into @TestTable
SELECT 1, 'A' union all
SELECT 1, 'A' union all
SELECT 1, 'B' union all
SELECT 2, 'A' union all
SELECT 2, 'B' union all
SELECT 3, 'A' union all
SELECT 3, 'B'

SELECT t.col1, STUFF((SELECT ',' + s.Col2 FROM @TestTable s WHERE s.col1 = t.col1 FOR XML PATH('')),1,1,'') AS CSV
FROM @TestTable AS t
GROUP BY t.col1

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources