Combine Rows into String in SQL Server

Last Updated : 26 May, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

To combine rows into a string in SQL Server, use the SQL COALESCE() function or the SQL CONCAT() function.

COALESCE() function in SQL is used to handle null values. It returns non-null values from a row, which can be concatenated into string.

CONCAT() function in SQL is used to concatenate two or more strings into a single string.

Here we will learn how to combine rows into a string in SQL server using SQL COALESCE() function and SQL CONCAT function.

How to Combine Rows into String in SQL Server

There are two methods to combine rows into string in SQL Server:

Let's learn each of these methods, with an example:

Demo Table

Let us suppose we have below table named "geek_demo" -

FirstNameLastNameSalaryCity
AnkitGupta24500Delhi
BabitaDutta23600Noida
ChetanJain25600Noida
DeepakSaini24300Delhi
IshaSharma25900Delhi
KhushiSingh24600Noida
MeghaGoyal25500Noida
ParulKumari23900Noida

Using COALESCE() Function

To combine multiple rows into a single string using the COALESCE function in SQL Server, first, declare a variable, use a SELECT statement with COALESCE to concatenate the values, and then SELECT the concatenated string.

Query to Concatenate Rows into String using COALESCE() function 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    

Using CONCAT Function

To combine multiple rows into a single string using the COALESCE function in SQL Server, first, declare variable, use CONCAT() function to concatenate the values into single string and finally display the results.

Query to Concatenate Rows into String using CONCAT() function 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 

Next Article
Article Tags :

Similar Reads