Combine Rows into Strings in SQL, Server

In SQL Server, there are a number of different techniques to combine records into strings.

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




First approach: COALESCE

To combine many rows into a single string using the COALESCE method, first, create a variable of type varchar to hold the coalesced strings, then use a comma to separate each row's string values. Finally, apply the COALESCE to the variable.


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;
  • In the above query, SQL COALESCE function is used to combine all rows values.

  • Declare statement is used to declare a variable to store the combined value of all rows

  • SQL select statement is used to select and store variables.

  • last SQL select statement is used to retrieve variable data.



Second approach: XML PATH

Versions 2005 and above of SQL Server support this XML PATH clause. The XML element is returned as a result set by SQL Server's FOR XML PATH function. In order to create a single row, it is utilized to combine or concatenate many columns. Each record is created as an XML element using the XML PATH clause, and the column becomes a nested element.


SELECT  column_name1,column_name2,..
FROM   table_name  FOR XML PATH('');
  • In the above query, SQL XML PATH clause is used to combine all rows values

  • SQL select statement is used to select all city values and convert them into XML tags

  • each row value is an in-between XML tag


SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, '' ) FROM temp1 GROUP BY id

Third approach: Using STRING_AGG

SQL Server 2017 and newer versions support the STRING AGG function. A built-in string function called STRING AGG is used to combine numerous rows of data into a single string. This function concatenates all of the expressions from the rows into a single string after converting them to string type.


STRING_AGG ( expression, separator ) [ WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]
  • The SQL STRING AGG function is used in the query above to aggregate the values from column across all rows.

  • Using the SQL select command and using the STRING AGG() function, all values are combined and separated by commas (,).


STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=   
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )


In this segment, we came to know about the three different methods used to combine rows with ease in SQL.

Updated on: 06-Apr-2023

10K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started