SELECT as Delimited String - 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)

SELECT as Delimited String

Author

Topic

12many
Starting Member

9 Posts

Posted - 2013-03-14 : 06:53:32

Hi There

I have a Table Groupmembers

GroupId | Member
1 5
1 6
1 7
2 8
2 9
2 10

I would like a result Like

Group | GroupMembers
1 5,6,7
2 8,9,10

I found this query on line and it does return the delimited members

DECLARE @list VARCHAR(MAX)
SELECT @list = COALESCE(@listStr+',' ,'') + CONVERT(VARCHAR(4),member)
FROM groupmembers
WHERE group = 1
SELECT @list

The problem here is that i can't then use this as a sub query.

Can any one Help

Many thanks

Ian

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 07:31:20

SELECT t1.GroupId, STUFF((SELECT ',' + CAST(s.Member AS VARCHAR) FROM @t s WHERE s.GroupId = t1.GroupId FOR XML PATH('')),1,1,'') AS CSV
FROM @t t1
GROUP BY t1.GroupId

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources