If you are on SQL Server 2017 or Azure, see 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 (''), TYPE ).value('text()[1]','nvarchar(max)') [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 (''), TYPE ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students] FROM dbo.Students ST2