How to concatenate text from multiple rows into a single text string in SQL Server

ID : 389

viewed : 294

Tags : sqlsql-servercsvstring-concatenationgroup-concatsql





Top 5 Answer for How to concatenate text from multiple rows into a single text string in SQL Server

vote vote

96

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 
vote vote

85

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 @Names NULL 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]).

vote vote

74

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.

STRING_AGG (Transact-SQL)

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; 
vote vote

67

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 
vote vote

58

In SQL Server 2005

SELECT Stuff(   (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') 

In SQL Server 2016

you can use the FOR JSON syntax

i.e.

SELECT per.ID, Emails = JSON_VALUE(    REPLACE(      (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)     ,'"},{"_":"',', '),'$[0]._' )  FROM Person per 

And the result will become

Id  Emails 1   abc@gmail.com 2   NULL 3   def@gmail.com, xyz@gmail.com 

This will work even your data contains invalid XML characters

the '"},{"_":"' is safe because if you data contain '"},{"_":"', it will be escaped to "},{\"_\":\"

You can replace ', ' with any string separator


And in SQL Server 2017, Azure SQL Database

You can use the new STRING_AGG function

Top 3 video Explaining How to concatenate text from multiple rows into a single text string in SQL Server







Related QUESTION?