Concatenate text from multiple rows into a single text string

[ForumBot]

Concatenate text from multiple rows into a single text string

[ForumBot]

If you are on SQL Server 2017 or Azure, see [Mathieu Renda answer](https://stackoverflow.com/a/42778050/1178676).

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 ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
GROUP BY ST2.SubjectID
) [Main]

```

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

```
SELECT ST2.SubjectID,
STUFF(
(
SELECT ',' + ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 1, 1, '') [Students]
FROM dbo.Students ST2
GROUP BY ST2.SubjectID

```