For an instructor lead, in-depth look at learning SQL click below.
The STRING_AGG is a built-in function in SQL Server (starting with 2017) that concatenates the values of string expressions and places a separator between them. This function simplifies the concatenation of strings. Prior to SQL Server 2017, you had to use various workarounds to concatenate string values with a certain separator. Now with STRING_AGG, it’s just a piece of cake.
The Syntax
|
1 2 3 |
STRING_AGG ( expression, separator ) [ WITHIN GROUP ( <order_by_clause> ) ] |
Where, ‘expression’ is the column that receives data for concatenating, ‘separator’ is the symbol or string that separates string values, and the optional ‘order_by_clause’ determines the sequence of concatenated values.
Examples
Basic Example:
Assume we have a table called ‘Orders’ like this:
|
1 2 3 4 5 6 7 8 9 |
OrderID Product 1 A 1 B 2 C 2 D 2 E 3 F |
And we want to aggregate the ‘Product’ values for each ‘OrderID’. We can use the STRING_AGG function like follows:
|
1 2 3 4 5 |
SELECT OrderID, STRING_AGG(Product, ', ') AS Products FROM Orders GROUP BY OrderID; |
This will return:
|
1 2 3 4 5 6 |
OrderID Products 1 A, B 2 C, D, E 3 F |
With ORDER BY clause:
If we want to control the order of the aggregated strings, we can add the WITHIN GROUP clause like this:
|
1 2 3 4 5 |
SELECT OrderID, STRING_AGG(Product, ', ') WITHIN GROUP (ORDER BY Product ASC) AS Products FROM Orders GROUP BY OrderID; |
This will return:
|
1 2 3 4 5 6 |
OrderID Products 1 A, B 2 C, D, E 3 F |
Note that STRING_AGG returns a single string result, and it’s very helpful in many scenarios where you need to concatenate string values in a single row, eliminating the need for additional looping or variables handling inside your SQL scripts.