How to concat many rows into one string in SQL Server 2008?

Question

How to concat many rows into one strings?

Query:

SELECT name FROM mytable;

Result:

name
----
kim
lee
park
cho

Just I want.

name
----
kim,lee,park,cho

Impossible?

Duplicate "MySQL : Multiple row as comma separated single row" — Hiren gardhariya, Aug 07 '13 at 11:36
http://stackoverflow.com/questions/5196371/sql-query-concatenating-results-into-one-string — garf1eld, Aug 07 '13 at 11:37

score 46 · Accepted Answer · answered Aug 07 '13 at 11:36

46

Try this one -

DECLARE @temp TABLE (name NVARCHAR(50))

INSERT INTO @temp (name)
VALUES ('kim'),('lee'),('park'),('cho')

SELECT STUFF((
    SELECT ',' + name
    FROM @temp
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Output -

kim,lee,park,cho

answered Aug 07 '13 at 11:36

Devart

119,203
23
166
186

11

An explanation of how this works would be helpful for future visitors. – Nubcake Jun 28 '17 at 22:54

score 10 · Answer 2 · edited Jul 18 '16 at 17:49

10

I have been working on something similar this morning, adding the case will also work for 1 returned row. Hope this helps. Else follow the link the first comment sent you.

Declare @NameList VarChar(MAX) = ''    
select @NameList = 
    case when @NameList = ''
        then coalesce(Name, '')
        else @NameList + coalesce(',' + Name, '')
    end
from mytable
print @NameList

Ben

edited Jul 18 '16 at 17:49

palota

465
4
8

answered Aug 07 '13 at 11:44

Ben Barter

159
1
10

score 8 · Answer 3 · answered Aug 07 '13 at 11:36

8

Try this

 SELECT   
name= substring((SELECT ( ', ' + Name)
                       FROM TableName t
                       FOR XML PATH( '' )
                      ), 3, 1000 )  FROM mytable tn

answered Aug 07 '13 at 11:36

Amit

15,217
8
46
68

How to concat many rows into one string in SQL Server 2008?

3 Answers3

Linked