Open In App

Combine Rows into String in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 09 Mar, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads