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
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;
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