STRING_AGG() in SQL
Concatenates values from multiple rows into a single string, with specified delimiter.
Syntax
Return type
STRING_AGG() Function Example
What is STRING_AGG() in SQL?
The STRING_AGG()
function in SQL concatenates values from multiple rows into a single string, using a specified delimiter. It is commonly used for aggregating text data, combining related records, and generating comma-separated lists.
This function is supported in SQL Server (2017+), PostgreSQL (9.0+), and Azure SQL. For MySQL and SQLite, use GROUP_CONCAT()
instead.
The STRING_AGG()
function is widely used in data aggregation, reporting, and text-based analytics.
Parameters:
expression: Column or expression to concatenate
delimiter: Separator between concatenated values
ORDER BY (optional): Sort values before concatenation
Example Use Cases:
-- Combine employee names by department
-- Create comma-separated list of products
-- Aggregate tags with custom ordering
Notes:
Behavior: NULL handling varies by DBMS
Performance Considerations: May have length limitations
Version Info: Modern replacement for older GROUP_CONCAT
Deprecated/Recommended Alternatives: Use LISTAGG in Oracle/Snowflake
Error Handling:
Error: Returns NULL if all input values are NULL
Recommendation: Use COALESCE for NULL handling
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
GROUP_CONCAT(expression)
GROUP_CONCAT(name)
Ignores NULL
PostgreSQL
STRING_AGG(expression, delimiter)
STRING_AGG(name, ',')
Ignores NULL
SQL Server
STRING_AGG(expression, delimiter)
STRING_AGG(name, ',')
Ignores NULL
SQLite
GROUP_CONCAT()
GROUP_CONCAT(name)
Ignores NULL
BigQuery
STRING_AGG(expression, delimiter)
STRING_AGG(name, ',')
Ignores NULL
Snowflake
LISTAGG(expression, delimiter)
LISTAGG(name, ',')
Ignores NULL
Athena
ARRAY_JOIN(ARRAY_AGG())
ARRAY_JOIN(ARRAY_AGG(name), ',')
Ignores NULL