Help with Writing a Query to Combine Multiple Rows into One in SQL Server

Question

Ravi Vishwakarma · Answer

Combining multiple rows into one in SQL Server can be achieved using the 
STRING_AGG function (available from SQL Server 2017 onwards) or using the 
FOR XML PATH method for earlier versions. Here are examples of both methods.
Using STRING_AGG (SQL Server 2017+)
Suppose you have a table your_table with a column your_column, and you want to concatenate all the values in
your_column into a single string:
--Syntax 
SELECT STRING_AGG(your_column, ', ') AS concatenated_string
FROM your_table;
--Example
SELECT STRING_AGG(ID, ', ') AS concatenated_string
FROM Article;
Using FOR XML PATH (SQL Server 2016 and earlier)
If you're using a version of SQL Server before 2017, you can achieve the same result using the
FOR XML PATH method:
--Syntax
SELECT STUFF(
 (SELECT ', ' + your_column
 FROM your_table
 FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)'),
 1, 2, ''
) AS concatenated_string;
--Example
SELECT STUFF(
 (SELECT ', ' + CAST(ID AS VARCHAR)
 FROM Article
 FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)'),
 1, 2, ''
) AS concatenated_string;
Explanation
STRING_AGG method:
STRING_AGG(your_column, ', '): Concatenates the values of 
your_column with a comma and a space as the separator.
FOR XML PATH method:
(SELECT ', ' + your_column FROM your_table FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'): Creates an XML string of the concatenated values, each prefixed with a comma and a space.STUFF(..., 1, 2, ''): Removes the first comma and space from the concatenated string.
and 
Read more
Explain the SQL CURSOR with an example.
How can I optimize SQL Server queries to improve performance?
How do I handle NULL values in SQL Server queries and avoid
SQL Query to Get Yearly Aggregated Data in SQL Server

forum

Help with Writing a Query to Combine Multiple Rows into One in SQL Server

Ravi Vishwakarma

1 Answers

Using `STRING_AGG` (SQL Server 2017+)

Using `FOR XML PATH` (SQL Server 2016 and earlier)

Explanation

Liked By