Combine Rows into String in SQL Server

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