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