Concatenate many rows into a single text string?
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?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 subquery:
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
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
