Combine Rows into String in SQL Server

To combine rows into a string in SQL Server, use the SQL COALESCE() function or the SQL CONCAT() function.

COALESCE() function in SQL is used to handle null values. It returns non-null values from a row, which can be concatenated into string.

CONCAT() function in SQL is used to concatenate two or more strings into a single string.

Here we will learn how to combine rows into a string in SQL server using SQL COALESCE() function and SQL CONCAT function.

There are two methods to combine rows into string in SQL Server:

• Using SQL COALESCE() function
• Using SQL CONCAT() function

Let’s learn each of these methods, with an example:

Demo Table

Let us suppose we have below table named “geek_demo” –

Using COALESCE() Function

To combine multiple rows into a single string using the COALESCE function in SQL Server, first, declare a variable, use a SELECT statement with COALESCE to concatenate the values, and then SELECT the concatenated string.

Query to Concatenate Rows into String using COALESCE() function in SQL Server:

DECLARE @Names VARCHAR(MAX)  
SELECT @Names = COALESCE(@Names + ', ', '') + [FirstName] 
FROM [geek_demo]
SELECT @Names AS [List of All Names]

Output:

Using CONCAT Function

To combine multiple rows into a single string using the COALESCE function in SQL Server, first, declare variable, use CONCAT() function to concatenate the values into single string and finally display the results.

Query to Concatenate Rows into String using CONCAT() function in SQL Server:

DECLARE @FirstNames VARCHAR(MAX)
DECLARE @LastNames VARCHAR(MAX)
SELECT @FirstNames = CONCAT(@FirstNames + ', ', '') + [FirstName] 
FROM [geek_demo]  
SELECT @LastNames = CONCAT(@LastNames + ', ', '') + [LastName] 
FROM [geek_demo]
SELECT @FirstNames AS [List of First All Names],
   @LastNames AS [List of All Last Names]

Output :