In SQL Server, how do you concatenate many rows into a single text string?

Question

Say you have a table Names that has three rows:

Peter Paul Mary

Is there an easy way to turn this into a single string of "Peter, Paul, Mary"?

Answer 1

Depends on your database vendor. MySQL has concat_ws. MS SQL Server expects you to do it in your client application.

Update: you could also do it in an external procedure or UDF, perhaps by using a cursor or calling out to CLR code.

Answer 2

One way you could do in ms sql server would be to return the table content as xml (for xml raw), convert the result to a string and then replace the tags with ", "

Answer 3

For answers specific to SQL Server, try this question.

Answer 4

In MySQL there is a funciton GROUP_CONCAT() which allows you to concat the values from multiple rows. Example:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

Answer 5

I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

Answer 6

Use COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

Answer 7

DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

This puts the stray comma at the beginning

However, if you need other columns, or to CSV a child table you need to wrap this in scalar UDF

You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because google doesn't do work stuff at home :-)

Answer 8

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

Select Main.SubjectID,
       Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID, 
           (Select ST1.StudentName + ',' AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')) [Students]
     From dbo.Students ST2) [Main]

Answer 9

In Sql 20105,

SELECT Stuff((SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')