The STRING_AGG function is used in SQL to concatenate values from multiple rows into a single string. It is particularly useful when you want to combine values from a column across multiple rows of a query result.
Syntax
The syntax for the STRING_AGG function is as follows:
STRING_AGG(expression, separator)
- expression: The column or value you want to concatenate.
- separator: A string that will be used to separate the concatenated values.
Example
Let's say you have a table called employees with the following data:
| id | name | department |
|----|------------|------------|
| 1 | Alice | HR |
| 2 | Bob | Engineering |
| 3 | Carol | HR |
| 4 | David | Engineering |
| 5 | Eve | HR |
If you want to concatenate the names of employees by their department, you can use STRING_AGG as follows:
SELECT
department,
STRING_AGG(name, ', ') AS employee_names
FROM
employees
GROUP BY
department;
Result
The above query would produce a result like:
| department | employee_names |
|--------------|--------------------------|
| HR | Alice, Carol |
| Engineering | Bob, David |
Additional Considerations
1. Ordering: If you want to specify the order of the concatenated values, you can use the ORDER BY clause inside the STRING_AGG.
SELECT
department,
STRING_AGG(name, ', ' ORDER BY name) AS employee_names
FROM
employees
GROUP BY
department;
2. Null Handling: STRING_AGG ignores NULL values in the concatenation process.
Compatibility
The STRING_AGG function is available in SQL Server 2017 and later, PostgreSQL, and some other SQL databases. Always check the documentation for your specific SQL dialect for any variations in syntax or functionality.