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.

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