Combine Rows into String in SQL Server
Last Updated :
26 May, 2024
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" -
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 |
Similar Reads
Replace String in SQL Server In SQL Server, manipulating data from a table and applying functions to modify values dynamically is a common task. One such example involves replacing parts of strings, such as email domains. This content demonstrates how string replacement can be applied to a dataset that includes employee informa
2 min read
STR() Function in SQL Server The STR() function converts a numeric value to a character value. Syntax : STR(float_expression [, length [, decimal]]) Parameter : This method accepts three parameters as mentioned above and described below : float_expression : It is a numeric expression that evaluates to an approximate number with
1 min read
Reverse a string in PL/SQL Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given a string, the task is to reverse a string using P
1 min read
Concatenation of strings in PL/SQL Prerequisite - PL/SQL Introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given two strings and the task is to concatenate them a
1 min read
How to Turning a Comma Separated String into Individual Rows in SQL Server? Given a column in the table having strings with comma-separated values. The task is to split all the comma-separated values and insert them as new rows. So, let's start by creating a database first. Step 1: Create a Database. Query : CREATE DATABASE GFGStep 2: Use the GFG Database. Query : USE GFGSt
1 min read
SQL Query to Convert Rows to Columns in SQL Server In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable. For example, Suppose we h
2 min read