Concatenate Multiple SQL Rows Into Single String

SQL

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:
Concat Cities into one row MS SQL

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.
FOR XML PATH concat rows
SELECT STUFF( ( SELECT ',' + CityName FROM .. 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.
COALESCE concatenate rows in single string
DECLARE @cities VARCHAR(MAX); SELECT @cities = COALESCE(@cities + ', ' + CityName, CityName) FROM .. SELECT @cities;

 

Concatenate Rows Using STRING_AGG - Method 3

The MS SQL Server 2017 version has a new function STRING_AGG(expression, separator).
STRING_AGG concatenete rows in single string

Watch my video with examples:

You may also be interested to read my articles related to MS SQL Server: