A few days ago, I needed to concatenate multiple rows into a single line of text. This was necessary to build a report. And I want to show you three ways to do it. We will concatenate CityName column into a single string, separating each city with a comma. This is what the table of cities looks like:
Concatenate Rows Using FOR XML PATH – Method 1
The first way is to use FOR XML PATH
. This method works in MS SQL Server version 2005 and higher. Adding FOR XML PATH
to the end of a query return results as XML elements. The element name is contained in the PATH argument. And instead of element name, we specify empty string ''
. The STUFF
function removes the first comma from the result.
SELECT STUFF( ( SELECT ',' + CityName FROM [YarkulTestDb1].[dbo].[City] FOR XML PATH('') ), 1 , 1, '') As Cities
Concatenate Rows Using COALESCE – Method 2
Using the COALESCE
function, you can also concatenate the results into a single string. This is done by declaring the @cities varchar variable and using that variable inside the COALESCE
function. The trailing comma will be removed automatically since @cities + ', ' + CityName
will initially be NULL
. This COALESCE
method can be used in SQL Server 2008 and later.
DECLARE @cities VARCHAR(MAX); SELECT @cities = COALESCE(@cities + ', ' + CityName, CityName) FROM [YarkulTestDb1].[dbo].[City] SELECT @cities;
Concatenate Rows Using STRING_AGG – Method 3
The MS SQL Server 2017 version has a new function STRING_AGG(expression, separator).
Watch my video with examples:
You may also be interested to read my articles related to MS SQL Server: