- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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.
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.
Kickstart Your Career
Get certified by completing the courseGet Started