SQL Concatenate Rows into String

How to write a query to Concatenate Rows in SQL Server to form a String with example?. It is one of the common SQL Interview Questions that you might face in the interviews.

For this SQL server 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.

-- Query to Concatenate Rows in SQL Server
USE [SQL Tutorial]
GO
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

-- Query to Concatenate Rows in SQL Server
USE [SQL Tutorial]
GO
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 do Concatenate Rows into string using the CONCAT Function in Sql Server.

-- Query to Concatenate Rows in SQL Server
USE [SQL Tutorial]
GO
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

-- Query to Concatenate Rows in SQL Server
USE [SQL Tutorial]
GO
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.

-- Query to Concatenate Rows in SQL Server
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

-- Query to Concatenate Rows in SQL Server
SELECT STUFF((
    SELECT ',' + SPACE(1) + [FirstName],
	   ',' + SPACE(1) + [LastName]
    FROM [Customers]
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
	AS [List Of Names]