How to combine rows into one string in SQL [SOLVED]


SQL

Reviewer: Deepak Prasad

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

How to combine rows into one string in SQL [SOLVED]

Result table

How to combine rows into one string in SQL [SOLVED]

Subject table

How to combine rows into one string in SQL [SOLVED]

 

Different methods to combine rows into a string which we will study in this tutorial:

  1. COALESCE
  2. XML PATH
  3. 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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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:

How to combine rows into one string in SQL [SOLVED]

 

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

SQL COALESCE

 

Read More

STRING_AGG

Falguni Thakker

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on her LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

1 thought on “How to combine rows into one string in SQL [SOLVED]”

Leave a Comment