How to Concatenate Multiple Rows Into Single String SQL Server.

Sometimes we need to concatenate multiple rows into a single text string by using a SQL query, in this blog post I will show you four ways to achieve this, the first three examples use SQL Server 2014 and the last example uses SQL Server 2019.

Concatenate Rows Using COALESCE

You can use the COALESCE method to concatenate rows and columns into a single text string, this method is available in SQL server 2008 and higher and the code below shows how to do this. I'm using the same tables as previous SQL server blogs and the code for creating the tables and populating them can be found here.  

The COALESCE method is below.

Select FirstName, LastName, CityName, PostCode from SQLDemo.dbo.PersonNameAndAddress
 
Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode, FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode)
        From SQLDemo.dbo.PersonNameAndAddress 
		Select @val  AS PersonNameAndAddress;

Running the above code will produce the following results as a string in SQL server.

Concatenate Rows Using COALESCE

Concatenate Multiple Rows Using FOR XML PATH

The next method is to use the XML Path, this method can be found in SQL Server 2005 and higher. In this example, I use the same code as above, but I use a substring to remove the leading comma and set the length of the string to 9999 characters.

Select SUBSTRING( 
( 
     SELECT ',' + FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode AS 'data()'
         FROM SQLDemo.dbo.PersonNameAndAddress FOR XML PATH('') 
), 2 , 9999) As PersonNameAndAddress

The results running the above code is shown below:

Concatenate Multiple Rows Using FOR XML PATH

If you notice the results have a space before every comma, this is because I used a column name data(), when you specify data(), the value is treated as an atomic value and a space is added to the XML.

If that is not what you require, then you can use the code below which will return a string in SQL server:

SELECT PersonNameAndAddress = STUFF((
       SELECT  ',' + FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode FROM SQLDemo.dbo.PersonNameAndAddress FOR XML PATH('') 
), 1 , 1, '')

The above code returns the same results but without the space before the comma.

The STUFF() select function deletes a part of a string and then inserts another part into the string, starting at a specified position.

Using STRING_AGG

This next example is only available in SQL Server 2017 and above

The final method uses the STRING_AGG function and the code for that is below:

SELECT STRING_AGG( ISNULL(FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode, ' '), ',') As PersonNameAndAddress 
       From dbo.PersonNameAndAddress

Once again I'm joining the columns and separating each person's details with a comma and the results are shown below:

Using STRING_AGG 

The STRING_AGG is a string function that is designed to do what we require when we need to concatenate multiple rows into a single string.

I hope you found this blog helpful and thanks for reading.