The STRING_AGG function in SQL is used to concatenate values from multiple rows into a single string, with a specified separator between the concatenated values. It is particularly useful when working with groups of data in SQL queries.
Here is the general syntax for using STRING_AGG:
STRING_AGG(expression, separator)
- expression: The column or expression whose values you want to concatenate.
- separator: The string that is inserted between each value in the result. This is optional and can be a comma, space, or any other string.
Example Usage
Imagine you have a table named Employees with the following data:
| Department | EmployeeName |
|------------|-----------------|
| Sales | Alice |
| Sales | Bob |
| Marketing | Carol |
| Marketing | Dave |
| IT | Eva |
If you want to concatenate employee names for each department, you can use STRING_AGG in the following way:
SELECT
Department,
STRING_AGG(EmployeeName, ', ') AS EmployeeNames
FROM
Employees
GROUP BY
Department;
Result
The resulting output will look like this:
| Department | EmployeeNames |
|------------|------------------------|
| Sales | Alice, Bob |
| Marketing | Carol, Dave |
| IT | Eva |
Important Notes
- The STRING_AGG function is available in SQL Server (2017 and later), PostgreSQL, and other SQL dialects. Make sure your database supports it.
- In case of an empty result set or no matching rows, STRING_AGG will return NULL, unless handled with COALESCE or other expressions.
This example demonstrates how to effectively use the STRING_AGG function to achieve concatenation of string values across rows based on a grouping condition (in this case, the department).