How STRING_AGG works in SQL? Best STRING_AGG examples

How STRING_AGG works in SQL? Best STRING_AGG examples

When working with SQL, one common requirement is to concatenate multiple rows into a single string. Traditionally, achieving this required complicated solutions, but with the introduction of STRING_AGG(), handling row aggregation became significantly easier. Let’s dive into how STRING_AGG() works and explore some of the best examples of using it.

What is STRING_AGG?

STRING_AGG() is a built-in aggregate function in SQL Server (2017+), PostgreSQL (9.0+), and other modern SQL-based databases. It helps concatenate values from multiple rows into a single string while allowing for a defined separator between values.

Basic Syntax of STRING_AGG

The basic syntax of STRING_AGG() in SQL Server and PostgreSQL is:

STRING_AGG(expression, separator)  
[ ORDER BY expression [ ASC | DESC ]]
  • expression – The column or value to aggregate.
  • separator – The character(s) used to separate aggregated values.
  • ORDER BY (optional) – Defines the order of concatenated values.

Simple Example: Using STRING_AGG()

Consider a table named employees with the following records:

ID Department Name
1 IT Alice
2 IT Bob
3 HR Charlie

To retrieve a comma-separated list of employees in each department, we can use STRING_AGG():

SELECT 
    Department, 
    STRING_AGG(Name, ', ') AS EmployeeList
FROM employees
GROUP BY Department;

Result:

Department EmployeeList
IT Alice, Bob
HR Charlie

Using STRING_AGG() with ORDER BY

To maintain a specific ordering of values inside the aggregation, we can use the ORDER BY clause inside STRING_AGG():

SELECT 
    Department, 
    STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS EmployeeList
FROM employees
GROUP BY Department;

This ensures that names are concatenated in alphabetical order.

Advanced Use Case: Concatenating Multiple Columns

We can also concatenate multiple columns using STRING_AGG(). Suppose we want both the employee’s ID and name in the result:

SELECT 
    Department, 
    STRING_AGG(CONCAT(ID, ': ', Name), ', ') AS EmployeeList
FROM employees
GROUP BY Department;

Result:

Department EmployeeList
IT 1: Alice, 2: Bob
HR 3: Charlie

Handling NULL Values in STRING_AGG()

If a column contains NULL values, STRING_AGG() will ignore them. However, if you need to explicitly replace NULL with a default value before concatenation, you can use COALESCE():

SELECT 
    Department, 
    STRING_AGG(COALESCE(Name, 'Unknown'), ', ') AS EmployeeList
FROM employees
GROUP BY Department;

Performance Considerations

When using STRING_AGG() in large datasets, consider the following tips for optimization:

  1. Use indexes on the columns included in the GROUP BY clause.
  2. Avoid using STRING_AGG() on unfiltered large datasets to prevent memory overhead.
  3. Ensure that correct collation settings are used to avoid unexpected ordering issues.

Conclusion

The STRING_AGG() function simplifies row concatenation in SQL, making it a powerful tool for reporting and data aggregation. By using ORDER BY, handling NULL values carefully, and optimizing performance, you can maximize its benefits in your SQL queries. Mastering STRING_AGG() will let you manipulate data more effectively and simplify complex aggregations.

 

Other interesting article:

How MAX works in SQL? Best MAX examples