SQL Server: Concatenate Multiple Rows Into Single String

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 couple of the easiest techniques here.

Concatenate Multiple Rows Using FOR XML PATH

The simplest and straight forward way to concatenate a column into a string value 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 the column of country names 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

Using COALESCE

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

Using STRING_AGG

The STRING_AGG is a string function which will simplify the concatenation of columns. STRING_AGG is designed for this purpose. Unfortunately it will be available in the feature release of SQL Server. Currently it is NOT available even in SQL Server 2016. Anyway the syntax for using STRING_AGG is as below.

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

Reference

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


Leave your thoughts...