Tutorial Gateway

SQL Concatenate Rows into String

by

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

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

SQL Concatenate Rows into String 1

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

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]

OUTPUT

SQL Concatenate Rows into String 2

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

SQL Concatenate Rows into String 3

How to Concatenate Rows Example 2

In this example we 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]

OUTPUT

SQL Concatenate Rows into String 4

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 5

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]

OUTPUT

SQL Concatenate Rows into String 6

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

SQL Concatenate Rows into String 7