SQL Concatenate Rows into String

How to write a query to Concatenate Rows in SQL Server to form a String with an example?. It is one of the common Interview Questions that you might face in interviews. For this concat rows example, We use the below-shown data.

SQL Concatenate Rows into String Example

In this example, we will show you how to Concatenate Rows using the COALESCE Function and arithmetic operators. Please refer to the SQL COALESCE Function and SQL arithmetic operators.

DECLARE @LongStringFirstNames VARCHAR(MAX)  
SELECT @LongStringFirstNames = COALESCE(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers]
SELECT @LongStringFirstNames AS [List of Name]

Let me combine the Last Name rows as well

DECLARE @LongStringFirstNames VARCHAR(MAX)
DECLARE @LongStringLastNames VARCHAR(MAX)
  
SELECT @LongStringFirstNames = COALESCE(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers]  
SELECT @LongStringLastNames = COALESCE(@LongStringLastNames + ', ', '') + [LastName] FROM [Customers]
SELECT @LongStringFirstNames AS [List of First Name],
       @LongStringLastNames AS [List of Last Name]

How to Concatenate Rows into strings using concat function?

It will show you how to Concatenate Rows into strings using the CONCAT Function. Refer to the SQL CONCAT on the SQL tutorial page.

DECLARE @LongStringFirstNames VARCHAR(MAX)
  
SELECT @LongStringFirstNames = CONCAT(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers]  
SELECT @LongStringFirstNames AS [List of First Name]

Let me concatenate the Last Name rows as well

DECLARE @LongStringFirstNames VARCHAR(MAX)
DECLARE @LongStringLastNames VARCHAR(MAX)
  
SELECT @LongStringFirstNames = CONCAT(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers]  
SELECT @LongStringLastNames = CONCAT(@LongStringLastNames + ', ', '') + [LastName] FROM [Customers]
SELECT @LongStringFirstNames AS [List of First Name],
	   @LongStringLastNames AS [List of Last Name]

Concatenate Rows into String using space and XML PATH

In this example, we will show you how to concat rows using the SPACE and XML PATH. Refer to the SQL SPACE and SQL FOR XML PATH articles.

SELECT STUFF((
    SELECT ',' + SPACE(1) + [FirstName]
    FROM [Customers]
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
	AS [List Of First Names]

Let me combine the rows from two columns (First Name and Last Name) using the SQL STUFF function and FOR XML PATH. Also, check the SQL STUFF function.

SELECT STUFF((
    SELECT ',' + SPACE(1) + [FirstName],
	   ',' + SPACE(1) + [LastName]
    FROM [Customers]
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
	AS [List Of Names]