<t>If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.<br/>
<br/>
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.<br/>
<br/>
If there is a table called STUDENTS<br/>
<br/>
SubjectID StudentName<br/>
---------- -------------<br/>
1 Mary<br/>
1 John<br/>
1 Sam<br/>
2 Alaina<br/>
2 Edward<br/>
<br/>
```<br/>
<br/>
Result I expected was:<br/>
<br/>
```<br/>
SubjectID StudentName<br/>
---------- -------------<br/>
1 Mary, John, Sam<br/>
2 Alaina, Edward<br/>
<br/>
```<br/>
<br/>
I used the following `T-SQL`:<br/>
<br/>
```<br/>
SELECT Main.SubjectID,<br/>
LEFT(Main.Students,Len(Main.Students)-1) As "Students"<br/>
FROM<br/>
(<br/>
SELECT ST2.SubjectID,<br/>
(<br/>
SELECT ST1.StudentName + ',' AS [text()]<br/>
FROM dbo.Students ST1<br/>
WHERE ST1.SubjectID = ST2.SubjectID<br/>
ORDER BY ST1.SubjectID<br/>
FOR XML PATH (''), TYPE<br/>
).value('text()[1]','nvarchar(max)') [Students]<br/>
FROM dbo.Students ST2<br/>
GROUP BY ST2.SubjectID<br/>
) [Main]<br/>
<br/>
```<br/>
<br/>
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:<br/>
<br/>
```<br/>
SELECT ST2.SubjectID, <br/>
STUFF(<br/>
(<br/>
SELECT ',' + ST1.StudentName AS [text()]<br/>
FROM dbo.Students ST1<br/>
WHERE ST1.SubjectID = ST2.SubjectID<br/>
ORDER BY ST1.SubjectID<br/>
FOR XML PATH (''), TYPE<br/>
).value('text()[1]','nvarchar(max)'), 1, 1, '') [Students]<br/>
FROM dbo.Students ST2<br/>
GROUP BY ST2.SubjectID<br/>
<br/>
```</t>
