The STRING_AGG function is used in SQL to concatenate values from multiple rows into a single string, separating them with a specified delimiter. This function is typically available in SQL Server (2017 and later), PostgreSQL, and other database systems that support it.
Here’s how to use the STRING_AGG function in a SQL query:
Syntax
STRING_AGG(expression, delimiter)
- expression: The column or expression whose values you want to concatenate.
- delimiter: The string used to separate the concatenated values.
Example Usage
Assuming you have a table named Employees with a column FirstName and you want to concatenate all first names into a single string separated by commas:
SELECT STRING_AGG(FirstName, ', ') AS AllFirstNames
FROM Employees;
Sorting Concatenated Values
You can also sort the values before concatenation by using an ORDER BY clause within the STRING_AGG function. For example, if you want to concatenate the FirstName values in alphabetical order:
SELECT STRING_AGG(FirstName, ', ' ORDER BY FirstName) AS AllFirstNames
FROM Employees;
Grouping Results
If you need to concatenate values grouped by another column (e.g., department), you can use the GROUP BY clause. Here’s an example that concatenates employee names per department:
SELECT Department, STRING_AGG(FirstName, ', ') AS EmployeeNames
FROM Employees
GROUP BY Department;
Important Notes
- Ensure your SQL database version supports STRING_AGG. For instance, it's supported in SQL Server 2017 and later.
- In PostgreSQL, the syntax is similar but may have slight differences, so check the documentation for your specific database system.
Using the STRING_AGG function is a powerful way to provide summarized string output from multiple rows effectively.