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:
