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:
id |
name |
1 |
Alice |
2 |
Bob |
3 |
Charlie |
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:
customer_id |
product |
1 |
Laptop |
1 |
Mouse |
2 |
Keyboard |
2 |
Monitor |
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!