How to Use STRING_AGG() for Concatenating Rows into a Single Column in SQL Server

When working with SQL Server, you may encounter scenarios where you need to combine multiple row values into a single column. Prior to SQL Server 2017, this required using STUFF() with FOR XML PATH(), but now, the STRING_AGG() function provides a simpler approach.

What is STRING_AGG()?

The STRING_AGG() function concatenates values from multiple rows into a single string with a specified separator.

Basic Syntax:

SELECT STRING_AGG(column_name, ', ') AS concatenated_values
FROM table_name;

• column_name: The column whose values you want to concatenate.
• ', ': The separator used between values.

Example Usage

Consider a Customers table:

Using STRING_AGG(), we can concatenate the names:

SELECT STRING_AGG(name, ', ') AS customer_names
FROM Customers;

Result:

Alice, Bob, Charlie

Using STRING_AGG() with GROUP BY

You can also use STRING_AGG() within GROUP BY to aggregate data by a specific column. Consider an Orders table:

To get a list of products purchased by each customer:

SELECT customer_id, STRING_AGG(product, ', ') AS purchased_products
FROM Orders
GROUP BY customer_id;

Result:

customer_id | purchased_products
------------|-------------------
1           | Laptop, Mouse
2           | Keyboard, Monitor

Sorting Values in STRING_AGG()

By default, STRING_AGG() does not guarantee an order. To enforce ordering, use WITHIN GROUP (ORDER BY column_name). Example:

SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS sorted_names
FROM Customers;

Key Benefits of STRING_AGG():

• Eliminates complex workarounds like STUFF() with FOR XML PATH().
• More readable and concise syntax.
• Works efficiently with GROUP BY for aggregating related data.

STRING_AGG() is a powerful function that simplifies string concatenation in SQL Server, making queries cleaner and more efficient. Happy querying!