Open In App
Related Articles

Combine Rows into String in SQL Server

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
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” –

FirstNameLastNameSalaryCity
AnkitGupta24500Delhi
BabitaDutta23600Noida
ChetanJain25600Noida
DeepakSaini24300Delhi
IshaSharma25900Delhi
KhushiSingh24600Noida
MeghaGoyal25500Noida
ParulKumari23900Noida

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 NamesList 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 NamesList 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”

NameJoiningDateEmail
Ankit04 May 2018ankit@gfg.org
Babita09 July 2019babita@gfg.org
Chetan14 June 2017chetan@gfg.org
Deepak26 January 2019deepak@gfg.org
Isha24 May 2018isha@gfg.org
Khushi17 July 2018khushi@gfg.org
Megha03 March 2017megha@gfg.org
Parul20 December 2018parul@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 NamesList 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