SQL table can have more than one string-type column and a table can have multiple rows containing values in string-type columns. We can combine string-type rows data into one text such as combining the city name of all employees into a single string. In this tutorial we will learn how to concatenate text from multiple rows into a single text string in SQL Server
Consider student's result management database with three tables student, result, and subject to perform practical examples
student table
Result table
Subject table
Different methods to combine rows into a string which we will study in this tutorial:
- COALESCE
- XML PATH
- STRING_AGG
Method-1: Using COALESCE
To concatenate multiple rows into a single string using COALESCE method first we need to declare a variable of varchar type to store combined strings inside the coalesce, use a comma separator to differentiate each row string value in concated string then assign the COALESCE to the variable.
Syntax to use COALESCE
Select column_name1, column_name2,.. from table_name
Declare @variable1 Varchar(MAX),@variable2 Varchar(MAX),..;
Select @variable1 = COALESCE(@variable1 + ', ' + column_name1, column_name2)
From table_name Select @variable1;
Example-1: SQL combine rows into one string of single column using COALESCE
Write SQL query combine all student city data in a one string
Declare @city_name Varchar(MAX);
Select @city_name = COALESCE(@city_name + ' , ' + city,city)
From tblstudent Select @city_name as 'City Name of all Students';
- In the above query, SQL COALESCE function is used to combine all rows values of the city column.
- Declare statement is used to declare @city_name variable to store combined value of all rows
- SQL select statement is used to select and store all city names into @city_name variable
- last SQL select statement is used to retrieve data of @city_name variable data
OUTPUT:
Example-2: SQL combine rows into one string of multiple columns using COALESCE
Write SQL query to combine all students city and email data into two separated strings
Declare @city_name Varchar(MAX);
Declare @email_ID Varchar(MAX);
Select @city_name = COALESCE(@city_name + ' , ' + city,city), @email_ID = COALESCE(@email_ID + ' , ' + email,email)
From tblstudent Select @city_name as 'City Name of all Students', @email_ID as 'Email of all Students' ;
- In the above query, SQL COALESCE function is used to combine all rows values of city and email columns.
- Declare statement is used to declare
@city_name
and@email_ID
variables to store combined rows values of both columns - SQL select statement is used to select and store all city names into
@city_name
variable and all email ids in@email_ID
variable - last SQL select statement is used to retrieve values of
@city_name
and@email_ID
variables
OUTPUT:
Method-2: Using XML PATH
This XML PATH clause is supported in SQL Server version 2005 and higher. FOR XML PATH method in SQL Server returns a result set as the XML element. It is used to join or concatenate multiple columns into a single row. XML PATH clause creates each record as an element of XML and the column becomes a nested element.
Syntax to use XML PATH
SELECT column_name1,column_name2,..
FROM table_name FOR XML PATH('');
Example-1: SQL combine rows into one string of single column using XML PATH
Example 3: Write SQL query to combine all student's city name using XML PATH
SELECT city AS city
FROM tblstudent FOR XML PATH('');
- In the above query, SQL XML PATH clause is used to combine all rows values of the city column.
- SQL select statement is used to select all city values and convert into XML tags
<city>
- each row value is in-between XML tag of
<city>
OUTPUT:
Example-2: SQL combine rows into one string of multiple columns using XML PATH
Write SQL query to combine all student's city and emailed data into a single string
SELECT city, email AS email_ID
FROM tblstudent FOR XML PATH('');
- In the above query, SQL XML PATH clause is used to combine all rows data of city and email columns.
- SQL select statement is used to select all city and emailed column values and convert into XML tags
<city>
and<email>
- each row value of city and email is in-between XML tag of
<city>
and<email>
OUTPUT:
Method-3: Using STRING_AGG
SQL STRING_AGG function is supported by SQL Server version 2017 and higher. STRING_AGG is a built-in string function used to concatenate multiple rows of data into a single string. This function takes all expressions from rows, convert into string type, and concatenates them into a single string.
Syntax to use STRING_AGG
STRING_AGG ( expression, separator ) [ WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]
Example-1: SQL combine rows into one string of single column using STRING_AGG
Write SQL query to combine all student's city name
Select STRING_AGG(city, ',') As’ All student city name’
From tblstudent;
- In the above query, SQL
STRING_AGG
function is used to combine all rows data of city column. - SQL select statement is used to select all city values and
STRING_AGG()
function will combine all values separated by comma(,
).
OUTPUT:
Example-2: SQL combine rows into one string of multiple columns using STRING_AGG
Write SQL query to combine all student's city and email id column values into one string
Select STRING_AGG(city, ',') As 'All students city name',STRING_AGG(email,',') as 'All students email ID'
From tblstudents;
- In the above query, SQL
STRING_AGG
function is used to combine all rows data of city and email columns. - SQL select statement is used to select all city and email values and
STRING_AGG()
function will combine all values separated by comma(,
).
OUTPUT:
Example-3: SQL combine rows into one string using STRING_AGG with order by
Example 7: Write SQL query to combine all student's city column values into one string in descending order
Select STRING_AGG(city, ',') WITHIN GROUP (ORDER BY city DESC) As ' City Name of all Students'
From tblstudent;
- In the above query, SQL STRING_AGG function is used to combine all rows values of the city column.
- SQL select statement is used to select all city values, GROUP ORDER BY DESC clause is used to sort city names in descending order and
STRING_AGG()
function will combine all values separated by comma(,
).
OUTPUT:
Summary
In this article on SQL combining rows into one string, We have covered an overview of combine string rows, list out all three methods of combining rows into one string which are using COALESCE, USING XML PATH, and using string_AGG methods. Each method of combining rows values has been explained with syntax and practical examples with single columns and multiple columns.
References
Read More
Hi, how do i get the values to do not repeat in the cell