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?
sql
- asked 9 years ago
- B Butts
2Answer
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
- answered 8 years ago
- Sunny Solu
In MySQL there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
- answered 8 years ago
- G John
Best Rated Questions
Your Answer