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 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.
T-SQL CODE
|
1 2 3 4 5 6 |
-- 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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
-- 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 SQL Concatenate Rows into string using the CONCAT Function.
T-SQL CODE
|
1 2 3 4 5 6 7 |
-- 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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
-- 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.
T-SQL CODE
|
1 2 3 4 5 6 |
-- 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)
T-SQL CODE
|
1 2 3 4 5 6 7 |
-- 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
Leave a Reply