Mastering T-SQL: Combining Subquery Results for Comma-Separated Lists

Using STRING_AGG:

This method leverages the STRING_AGG function introduced in SQL Server 2017. It allows you to aggregate rows from a subquery and concatenate them with a specified delimiter.

Example:

-- Get order IDs for each customer
SELECT CustomerID,
       (
           SELECT STRING_AGG(OrderID, ',')
           FROM Orders o2
           WHERE o2.CustomerID = o1.CustomerID
       ) AS OrderIDs
FROM Customers o1;

This query:

1. Selects CustomerID from the Customers table.
2. Uses a subquery to select OrderID from the Orders table for each customer.
3. Within the subquery, STRING_AGG aggregates OrderID values, separating them with a comma (,).

Using FOR XML PATH:

This approach uses the FOR XML PATH functionality to convert subquery results into a single string with commas.

-- Get product names for each category
SELECT CategoryID,
       (
           SELECT STUFF((
               SELECT ', ' + Name
               FROM Products p2
               WHERE p2.CategoryID = p1.CategoryID
               FOR XML PATH('')
           ), 1, 2, '') AS ProductNames
       )
FROM Categories p1;

1. Within the subquery, FOR XML PATH('') converts each row into an XML fragment.
2. STUFF function removes the leading comma (', ') and combines the remaining fragments into a single string.

Related Issues and Solutions:

• Empty Subquery: If the subquery doesn't return any rows, the result might be an empty string or NULL depending on the chosen method. You can handle this by adding a check within the main query or using functions like ISNULL or COALESCE.
• Performance: For large datasets, STRING_AGG generally performs better than FOR XML PATH.