How to Concatenate Text from Multiple Rows into a Single Text String in Sql Server
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? 10 47...
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?
47 Answers
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
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Just some explanation (since this answer seems to get relatively regular views):
- Coalesce is really just a helpful cheat that accomplishes two things:
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
- The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make
@NamesNULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
SQL Server 2017+ and SQL Azure: STRING_AGG
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
Without grouping
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
With grouping:
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With grouping and sub-sorting
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
One method not yet shown via the XML data() command in SQL Server is:
Assume a table called NameList with one column called FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
returns:
"Peter, Paul, Mary, "
Only the extra comma must be dealt with.
As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
Must Read
In SQL Server 2005
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
