How to Combine Rows into String in SQL Server
There are two methods to combine rows into string in SQL Server:
- Using SQL COALESCE() function
- Using SQL CONCAT() function
Let’s learn each of these methods, with an example:
Demo Table
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 |
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 Names | List of All Last Names |
---|---|
Ankit , Babita , Chetan , Deepka , Isha , Khushi , Megha , Parul | Gupta , Dutta , Jain , Saini , Sharma , Singh , Goyal , Kumari |
Combine Rows into String in SQL Server
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.