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.

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 Example 2

It will show you, How to Concatenate Rows into strings using the CONCAT Function.

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 Example 3

In this example, we will show you, How to Concat Rows in SQL Server using the SPACE and XML PATH.

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.

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