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
?
csvgroup-concatsqlsql-serverstring-concatenation
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
?
Best Answer
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
Result I expected was:
I used the following
T-SQL
: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: