Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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
My Personal Notes arrow_drop_up
Recommended Articles
Page :