How to concatenate rows into string in Sql Server

Methods to concatenate rows into a Sql Server string.

Sometime ago I came with a similar problem where I had to concatenate multiple rows into a String, this for sending a file to other system in a CSV format (Comma separated values).


These kind of tasks are common when you are working with datasets as CSV format is quite common.

For this example We are going to use WideWorldImporters database. If you are interested in working with WideWorldImporters database follow the steps in the following link:  Restore WideworldImporters database

Ways to Split delimited column into multiple rows

String_AGG is a new method introduced in Sql Server 2017, this method allows us to create a list of values that comes from table rows.

We can group and order results depending in our needs, Syntax is pretty simple and functionality is complete, as you can see in the following example.

A) Concatenate rows using STRING_AGG without grouping

The following query is going to return the list of countries separated by comma(,), as you can see in the syntax  we didn’t apply grouping or ordering for this case, it is just a single list.

/*WITOUT GROUPING*/
SELECT STRING_AGG(CountryName,',') as Countries
FROM Application.Countries

B)Concatenate rows using STRING_AGG with grouping

If you wish you can apply grouping and also ordering to the results, in this case we are going to group results by continent and order them by country name.

/*WITH GROUPING AND ORDERING*/
SELECT Continent,STRING_AGG(CountryName,',') 
       WITHIN GROUP(ORDER BY CountryName DESC) As Countries
FROM Application.Countries
GROUP BY Continent

This is going to return a list of countries ordered in a descending way (starting from z to a) divided by continents (not ordered).

XML is a good option if you are using an old version of sql server, FOR XML PATH method can be used since Sql Server 2005.

A) Concatenate rows using XML without grouping

The following query will return the list of countries in a desc order.

/*WITOUT GROUPING*/
SELECT DISTINCT
       STUFF(
                 (
				  SELECT ',' + CountryName 
				  FROM Application.Countries as ct1
				  ORDER BY CountryName ASC
				  FOR XML PATH ('')	
				  ), 1, 1, ''
               )   AS  Countries 
FROM Application.Countries as ct2

B) Concatenate rows using rows using XML with grouping

This query is gong to return the same list than before but this time it is going to be grouped by Continent

/*WITH GROUPING AND ORDERING*/
SELECT Continent
       ,STUFF(
                 (
				  SELECT ',' + CountryName 
				  FROM Application.Countries as ct1
				  WHERE ct1.Continent = ct2.Continent
				  ORDER BY CountryName ASC
				  FOR XML PATH ('')				  
				  ), 1, 1, ''
				  
               )  AS  Countries 
FROM Application.Countries as ct2
GROUP BY Continent

JSON is available since version 2016, please prefer JSON than XML as it is faster.

  • Concatenate rows using JSON without grouping

The following query will return the list of countries in a desc order, as you can see syntax is pretty similar than XML

/*WITOUT GROUPING*/
SELECT 
	  JSON_VALUE(
		REPLACE(
			(
			SELECT _ = em.CountryName 
			FROM Application.Countries em 
			ORDER BY CountryName DESC
			FOR JSON PATH
			),'"},{"_":"',', '
		),'$[0]._'
) AS  Countries

  • Concatenate rows using JSON with grouping

This query is gong to return the same list than before  grouped by Continent

/*WITH GROUPING AND ORDERING*/
SELECT Continent
	  ,JSON_VALUE(
		REPLACE(
			(
			SELECT _ = em.CountryName 
			FROM Application.Countries em 
			WHERE em.Continent = per.Continent 
			ORDER BY CountryName DESC
			FOR JSON PATH
			),'"},{"_":"',', '
		),'$[0]._'
) AS  Countries 
FROM Application.Countries per
GROUP BY Continent

You can concatenate rows into a single string by using Scalar Variable method, there are several ways to do it but one of the most known is COALESCE method.

  • COALESCE Method

As you can see in the following example, you have to start by declaring a varchar variable(This variable is going to be max as we don’t know the size of the variable that we are going to need), then proceed to declare the varchar variable inside the coalesce, proceed to concat the variable with the column then assign the coalesce to a variable.

A lot of people thinks that coalesce is concatenating the rows but that is fake, coalesce is helping us to keep trailing comma on the list as the varchar variable is empty.

/*WITHOUT GROUPING*/
/*WITH COALESCE*/
DECLARE @COUNTRIES VARCHAR(MAX);
SELECT  @COUNTRIES = COALESCE(@COUNTRIES + ', ' + CountryName, CountryName) 
FROM Application.Countries 
ORDER BY CountryName DESC;
SELECT @COUNTRIES AS Countries;

As you can see we can use is null instead of coalesce and it is going to have the same effect.

  • CONCAT Method

Similar to coalesce method, but in this case we are not going to use coalesce, and we are going to use stuff function to get rid of the trim comma that is generated because of initial variable empty value.

/*WITHOUT GROUPING*/
/*WITH CONCAT*/
DECLARE @COUNTRIES VARCHAR(MAX);
SELECT  @COUNTRIES = CONCAT(@COUNTRIES ,', ') + CountryName
FROM Application.Countries 
ORDER BY CountryName DESC;
SELECT STUFF(@COUNTRIES,1,2,'') AS Countries;

 

Grouping this kind of function is hard, we have to start by creating a function for it, this function is going to retrieve country list by continent and use Coalesce Method (As it is the simplest one).

/*WITH GROUPING*/
/*CREATING FUNCTION*/
CREATE FUNCTION dbo.List(@CONTINENT VARCHAR(100))
RETURNS  VARCHAR(MAX)
AS
BEGIN
-- Defining Method
DECLARE @COUNTRIES VARCHAR(MAX);
SELECT  @COUNTRIES = CONCAT(@COUNTRIES + ', ', '') + CountryName
FROM Application.Countries 
WHERE Continent = @CONTINENT 
ORDER BY CountryName DESC;
--Returning list
RETURN @COUNTRIES;
END;

We are going to invoke the function for every Continent that we have, you can simulate the same behavior by using cursors.

WITH C AS(
    SELECT DISTINCT Continent
    FROM Application.Countries
)
SELECT *
FROM C 
CROSS APPLY (SELECT dbo.List(C.Continent) as List) D

This function returns the same results that we have with the previous methods but it involves more effort.

 


Leave a Reply

Your email address will not be published. Required fields are marked *