Combine Rows into String in SQL Server
Last Updated :
09 Mar, 2021
Imagine we need to select all the data from any given list. We could use multiple queries to combine rows in SQL Server to form a String.
Example-1 :
Let us suppose we have below table named “geek_demo” –
FirstName |
LastName |
Salary |
City |
Ankit |
Gupta |
24500 |
Delhi |
Babita |
Dutta |
23600 |
Noida |
Chetan |
Jain |
25600 |
Noida |
Deepak |
Saini |
24300 |
Delhi |
Isha |
Sharma |
25900 |
Delhi |
Khushi |
Singh |
24600 |
Noida |
Megha |
Goyal |
25500 |
Noida |
Parul |
Kumari |
23900 |
Noida |
Approach-1 :
In the below example, we will combine rows using the COALESCE Function.
Query to Concatenate Rows in SQL Server –
DECLARE @Names VARCHAR(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + [FirstName]
FROM [geek_demo]
SELECT @Names AS [List of All Names]
Output :
List of All Names |
Ankit , Babita , Chetan , Deepka , Isha , Khushi , Megha , Parul |
Approach-2 :
In the below example, we will combine the LastName rows also.
Query to Concatenate Rows in SQL Server –
DECLARE @FirstNames VARCHAR(MAX)
DECLARE @LastNames VARCHAR(MAX)
SELECT @FirstNames = COALESCE(@FirstNames + ', ', '') + [FirstName]
FROM [geek_demo]
SELECT @LastNames = COALESCE(@LastNames + ', ', '') + [LastName]
FROM [geek_demo]
SELECT @FirstNames AS [List of All First Names],
@LastNames AS [List of All Last Names]
Output :
List of All First Names |
List of All Last Names |
Ankit , Babita , Chetan , Deepka , Isha , Khushi , Megha , Parul |
Gupta , Dutta , Jain , Saini , Sharma , Singh , Goyal , Kumari |
Approach-3 :
In the below example, we will concatenate the Last Name rows as well.
Query to Concatenate Rows in SQL Server –
DECLARE @FirstNames VARCHAR(MAX)
DECLARE @LastNames VARCHAR(MAX)
SELECT @FirstNames = CONCAT(@FirstNames + ', ', '') + [FirstName]
FROM [geek_demo]
SELECT @LastNames = CONCAT(@LastNames + ', ', '') + [LastName]
FROM [geek_demo]
SELECT @FirstNames AS [List of First All Names],
@LastNames AS [List of All Last Names]
Output :
List of First All Names |
List of All Last Names |
Ankit , Babita , Chetan , Deepka , Isha , Khushi , Megha , Parul |
Gupta , Dutta , Jain , Saini , Sharma , Singh , Goyal , Kumari |
Approach-4 :
In the below example, we will combine the rows from two columns (FirstName & LastName) using the stuff function and for the XML path.
Query to Concatenate Rows in SQL Server –
SELECT STUFF((
SELECT ',' + SPACE(1) + [FirstName],
' ' + SPACE(1) + [LastName]
FROM [geek_demo]
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
AS [List Of All Names]
Output :
List Of Names |
Ankit Gupta , Babita Dutta , Chetan Jain , Deepka Saini , Isha Sharma , Khushi Singh , Megha Goyal , Parul Kumari |
Example-2 :
Let us suppose we have below table named “geek_demo1” –
Name |
JoiningDate |
Email |
Ankit |
04 May 2018 |
ankit@gfg.org |
Babita |
09 July 2019 |
babita@gfg.org |
Chetan |
14 June 2017 |
chetan@gfg.org |
Deepak |
26 January 2019 |
deepak@gfg.org |
Isha |
24 May 2018 |
isha@gfg.org |
Khushi |
17 July 2018 |
khushi@gfg.org |
Megha |
03 March 2017 |
megha@gfg.org |
Parul |
20 December 2018 |
parul@gfg.org |
Approach-1 :
In the below example, we will combine rows into a string using the CONCAT Function in SQL Server.
Query to Concatenate Rows in SQL Server –
DECLARE @Names VARCHAR(MAX)
DECLARE @Emails VARCHAR(MAX)
SELECT @Names = CONCAT(@Names + ', ', '') + [Name]
FROM [geek_demo1]
SELECT @Emails = CONCAT(@Emails + ', ', '') + [Email]
FROM [geek_demo]
SELECT @Names AS [List of All Names],
@Emails AS [List of All Emails]
Output :
List of All Names |
List of All Emails |
Ankit , Babita , Chetan , Deepka , Isha , Khushi , Megha , Parul |
ankit@gfg.org, babita@gfg.org, chetan@gfg.org, deepak@gfg.org, isha@gfg.org, khushi@gfg.org, megha@gfg.org, parul@gfg.org |
Approach-2 :
In the below example, we will combine rows in SQL Server using the SPACE and for XML path.
Query to Concatenate Rows in SQL Server –
SELECT STUFF((
SELECT ',' + SPACE(1) + [Email]
FROM [geek_demo]
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
AS [List Of All Emails]
Output :
List Of All Emails |
ankit@gfg.org, babita@gfg.org, chetan@gfg.org, deepak@gfg.org, isha@gfg.org, khushi@gfg.org, megha@gfg.org, parul@gfg.org |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...