Categories
csv group-concat sql sql-server string-concatenation

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

2255

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

  • 27

    For answers specific to SQL Server, try this question.

    Oct 12, 2008 at 0:03

  • 21

    For MySQL, check out Group_Concat from this answer

    – Pykler

    May 6, 2011 at 19:48


  • 29

    I wish the next version of SQL Server would offer a new feature to solve multi-row string concatination elegantly without the silliness of FOR XML PATH.

    Oct 2, 2014 at 11:47

  • 4

    Not SQL, but if this is a once-only thing, you can paste the list into this in-browser tool convert.town/column-to-comma-separated-list

    – Stack Man

    May 27, 2015 at 7:56

  • 4

    In Oracle you can use the LISTAGG(COLUMN_NAME) from 11g r2 before that there is an unsupported function called WM_CONCAT(COLUMN_NAME) which does the same.

    – Richard

    Jul 6, 2017 at 6:32

1605

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

21

  • 18

    Great solution. The following may be helpful if you need to handle special characters like those in HTML: Rob Farley: Handling special characters with FOR XML PATH(”).

    – user140628

    Apr 17, 2013 at 12:35

  • 13

    Apparently this doesn’t work if the names contain XML characters such as < or &. See @BenHinman’s comment.

    – Sam

    Aug 13, 2013 at 1:26

  • 29

    NB: This method is reliant on undocumented behavior of FOR XML PATH (''). That means it should not be considered reliable as any patch or update could alter how this functions. It’s basically relying on a deprecated feature.

    Nov 13, 2014 at 18:54

  • 33

    @Whelkaholism The bottom line is that FOR XML is intended to generate XML, not concatenate arbitrary strings. That’s why it escapes &, < and > to XML entity codes (&amp;, &lt;, &gt;). I assume it also will escape " and ' to &quot; and &apos; in attributes as well. It’s not GROUP_CONCAT(), string_agg(), array_agg(), listagg(), etc. even if you can kind of make it do that. We should be spending our time demanding Microsoft implement a proper function.

    Mar 23, 2015 at 14:15


  • 25

    Good news: MS SQL Server will be adding string_agg in v.Next. and all of this can go away.

    – Jason C

    Apr 6, 2017 at 0:32


1110

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]).

21

  • 78

    To be clear, coalesce has nothing to do with creating the list, it just makes sure that NULL values are not included.

    Feb 13, 2009 at 12:02

  • 21

    @Graeme Perrow It doesn’t exclude NULL values (a WHERE is required for that — this will lose results if one of the input values is NULL), and it is required in this approach because: NULL + non-NULL -> NULL and non-NULL + NULL -> NULL; also @Name is NULL by default and, in fact, that property is used as an implicit sentinel here to determine if a ‘, ‘ should be added or not.

    – user166390

    Aug 15, 2010 at 18:57


  • 68

    Please note that this method of concatenation relies on SQL Server executing the query with a particular plan. I have been caught out using this method (with the addition of an ORDER BY). When it was dealing with a small number of rows it worked fine but with more data SQL Server chose a different plan which resulted in selecting the first item with no concatenation whatsoever. See this article by Anith Sen.

    – fbarber

    Apr 26, 2012 at 2:18

  • 19

    This method cannot be used as a sub query in a select list or where-clause, because it use a tSQL variable. In such cases you could use the methods offered by @Ritesh

    Aug 2, 2013 at 8:10

  • 15

    This is not a reliable method of concatenation. It is unsupported and should not be used (per Microsoft, e.g. support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389). It can change without warning. Use the XML PATH technique discussed in stackoverflow.com/questions/5031204/… I wrote more here: marc.durdin.net/2015/07/…

    Jul 15, 2015 at 0:23


748

+150

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;

7

  • 3

    And, unlike CLR solutions, you have control over the sorting.

    – canon

    Jul 10, 2017 at 16:17

  • There seems to be a 4000 character display limitation on STRING_AGG

    Mar 3, 2020 at 5:04


  • 1

    Is there a way to do sorting in case there is no GROUP BY (so for the “Without grouping” example)?

    – RuudvK

    May 10, 2020 at 9:01

  • Update: I managed to do the following, but is there a cleaner way? SELECT STRING_AGG(Name, ‘, ‘) AS Departments FROM ( SELECT TOP 100000 Name FROM HumanResources.Department ORDER BY Name) D;

    – RuudvK

    May 10, 2020 at 9:11

  • 2

    I had to cast it to NVarchar(max) to get it work.. “` SELECT STRING_AGG(CAST(EmpName as NVARCHAR(MAX)), ‘,’) FROM EmpTable as t “`

    – Varun

    Sep 26, 2020 at 3:25