SQL Server Concatenate Multiple Rows into String 01

SQL Server: Concatenate Multiple Rows Into Single String

by

I came across a situation where I have to concatenate multiple rows (a column) into single string of text for reporting purpose. There are multiple ways to concatenate rows into string. Now we will see a couple of the easiest techniques here.

1. Concatenate Multiple Rows Using FOR XML PATH

The simplest and straight forward way to concatenate rows into a string value is to use FOR XML PATH in a select query. This FOR XML PATH method can be used in SQL Server version 2005 and higher.

In the below sample query, I’ve concatenated multiple rows of the column “CountryName” to a single string and added a comma between the country names. Then using a substring function, I’m removing the leading comma.

Select CountryName from Application.Countries; 

Select SUBSTRING( 
( 
     SELECT ',' + CountryName AS 'data()' 
         FROM Application.Countries FOR XML PATH('') 
), 2 , 9999) As Countries
SQL Server Concatenate Multiple Rows into String 01

NOTE: If you notice the result produced by this method, there is a space added after every country name. This is because of the path specified as column name is “data()”. Once the path specified as data(), the value is treated as an atomic value and a space char is added to the XML.

If you don’t need the trailing space and just want a comma along as a separator, then remove the “data()” part. An example query without data() part is below.

SELECT Countries = STUFF((
         SELECT ',' + CountryName
            FROM Application.Countries
            FOR XML PATH('')
         ), 1, 1, '')

2. Concatenate Rows Using COALESCE

You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.

In this method, you don’t need to worry about the trailing comma. You may need a stored procedure or a function to do this operation and get the concatenated string value.

Select CountryName from Application.Countries 

Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + CountryName, CountryName) 
        From Application.Countries Select @val;
SQL Server Concatenate Multiple Rows into String

3. Using STRING_AGG

The STRING_AGG is a string function which will simplify the concatenation of rows. STRING_AGG is designed for this purpose. Unfortunately it will be available in the feature release of SQL Server. This new function is available from SQL Server 2017 onwards. The syntax for using STRING_AGG is as below. Read more about using STRING_AGG here.

SELECT STRING_AGG( ISNULL(CountryName, ' '), ',') As Countries 
       From Application.Countries

Reference

  • Documentation of COALESCE at msdn.
  • Documentation of STRING_AGG at msdn.


25 thoughts on “SQL Server: Concatenate Multiple Rows Into Single String”

  1. If you initialize the variable and the values are different to null, you can avoid the coalesce

    DECLARE @x nvarchar(max) = ”
    DECLARE @tblError2 TABLE (ID INT IDENTITY(1,1) , Msg NVARCHAR(50))

    INSERT INTO @tblError2 VALUES (‘Error 1’)
    INSERT INTO @tblError2 VALUES (‘Error 2’)

    select * from @tblError2

    select @x = @x + ‘,’ + Msg from @tblError2

    select @x

    select * from @tblError2

    Reply

  8. Hi Beaulin,

    Can you help me too?

    I’m trying to use String_AGG function but it’s not working on SQL Server 18. It’s saying ” ‘STRING_AGG’ is not a recognized built-in function name.”

    Do you know any other way, like the ones above, of how to do this grouping by another column?

    Reply

    • HI,

      STRING_AGG is available in SQL Server 2017 and higher versions. You have mentioned that you are using “SQL Server 18” which is not available. After SQL Server 2017, Microsoft released 2019. In case if you have version 2017 or higher, then check the compatibility level of the database, it should be 140 or 150 or higher for STRING_AGG to work.

      Regards,
      Beaulin

      Reply

  12. In example1 there is a space after every country name. How do you get rid of that space (where does it come from)

    Reply

  13. Another option for row concatenation within categories, by using a correlated sub-query you can achieve similar levels of flexibility with a lot less code.

    SELECT DISTINCT STUFF(
    (SELECT ‘,’ + val AS ‘data()’
    FROM @t i
    WHERE i.categ = o.categ
    FOR XML PATH (”) ), 1, 1, ”),
    categ
    FROM @t o;

    Reply

  14. Here is another idea about how you can achieve the same using recursive CTE. Is very flexible and can be enhanced thoroughly, but I want to show only the proof of concept

    DECLARE @t TABLE(id INT PRIMARY KEY, val VARCHAR(MAX), categ INT)

    INSERT INTO @t VALUES
    (1, ‘abc’, 1),
    (2, ‘def’, 1),
    (3, ‘ghi’, 1),
    (4, ‘xyz’, 2),
    (5, ‘stu’, 2),
    (6, ‘prq’, 2);

    WITH a AS
    (SELECT row_number() OVER (PARTITION BY categ ORDER BY id ASC) rn_a,
    row_number() OVER (PARTITION BY categ ORDER BY id DESC) rn_d,
    id,
    CAST(val AS VARCHAR(MAX)) val,
    categ,
    @@ROWCOUNT rn_c
    FROM @t
    ),
    b AS
    (SELECT * FROM a WHERE rn_a = 1
    UNION ALL
    SELECT a.rn_a, a.rn_d, b.id, CAST(CONCAT(b.val, ‘ ‘, a.val) AS VARCHAR(MAX)), b.categ, @@ROWCOUNT rn_c
    FROM a JOIN b ON a.rn_a = b.rn_a + 1 AND a.categ = b.categ
    )
    SELECT * FROM b WHERE rn_d = 1

    Reply

  15. Hi, this is great. My doubt would be, what if we would like to have some 2 columns grouped? Say on this example, you would have another column with the continent and then the xml string with the countries within that group, can that be done using this function?
    Thanks

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.