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 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:
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.
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:
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.