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

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               Mary1               John1               Sam2               Alaina2               Edward

Result I expected was:

SubjectID       StudentName----------      -------------1               Mary, John, Sam2               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 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 ('')        ), 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 @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 + ', ', '') + NameFROM PeopleWHERE 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.

STRING_AGG (Transact-SQL)

Without grouping

SELECT STRING_AGG(Name, ', ') AS DepartmentsFROM HumanResources.Department;

With grouping:

SELECT GroupName, STRING_AGG(Name, ', ') AS DepartmentsFROM HumanResources.DepartmentGROUP BY GroupName;

With grouping and sub-sorting

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS DepartmentsFROM HumanResources.DepartmentGROUP BY GroupName;