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 −

• COALESCE

• XML PATH

• STRING_AGG

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.

Syntax

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.

Example

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

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.

Syntax

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

Example

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.

Syntax

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 (,).

Example

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

Conclusion

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