SQL Concatenate Rows into String

In this article we will show you, How to write a SQL Query to Concatenate Rows in SQL Server to form a String with example. This is one of the common SQL Interview Question that you might face in the interviews.

For this sql server concat rows example, We are going to use the below shown data

Above screenshot will show you the data inside the Customer table present in the SQL Tutorial database.

SQL Concatenate Rows into String Example 1

In this example we will show you, How to Concatenate Rows in SQL Server 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]

OUTPUT

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]

OUTPUT

Concatenate Rows in SQL Example 2

In this example we will show you, How to do Concatenate Rows into string using the CONCAT Function.

-- 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]

OUTPUT

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]

OUTPUT

SQL Concatenate Rows into String Example 3

In this example we will show you, How to Concatenate 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]

OUTPUT

Let me combine the rows from two columns (First Name, and Last Name) using the 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]

OUTPUT

Thank You for Visiting Our Blog