How to concatenate text from multiple rows into a single text string in SQL server?

itemprop="text">

Consider a database table holding
names, with three
rows:

Peter
Paul
Mary

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

itemprop="text">

class="normal">Answer

If you are
on SQL Server 2017 or Azure, see href="https://stackoverflow.com/a/42778050/1178676">Mathieu Renda answer.

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]

You can do the
same thing in a more compact way if you can concat the commas at the beginning and use
substring to skip the first one so you don't need to do a
sub-query:

SELECT DISTINCT
            ST2.SubjectID, 
 SUBSTRING(
 (
 SELECT ','+ST1.StudentName
            AS [text()]
 FROM dbo.Students ST1

 WHERE ST1.SubjectID =
            ST2.SubjectID
 ORDER BY ST1.SubjectID
 FOR XML PATH ('')

            ), 2, 1000) [Students]
FROM dbo.Students
            ST2