Back to Library

STRING_AGG() in SQL

Concatenates values from multiple rows into a single string, with specified delimiter.

Syntax

STRING_AGG(expression, delimiter) [WITHIN GROUP (ORDER BY expression)]
Return type
STRING/VARCHAR

STRING_AGG() Function Example

SELECT department, STRING_AGG(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

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

SELECT department, STRING_AGG(name, '; ') AS employee_list FROM employees GROUP BY department; 

-- Output:
department     employee_list
------------   ------------------------------------------------
Engineering    Alice Chen; Bob Smith; Carol Jones; David Kumar
Marketing      Emma Wilson; Frank Lee; Grace Martinez
Sales          Helen Brown; Ian Clark; Jack Thompson
HR             Karen Davis; Lisa Wang; Michael Roberts
IT             Noah Kim; Oliver Singh; Patricia Woods

-- Create comma-separated list of products 

SELECT category, STRING_AGG(product_name, ', ' ORDER BY price) AS products FROM products GROUP BY category; 
-- Output:
category        products
-------------   ------------------------------------------------
Electronics     Basic Mouse ($15), Keyboard ($45), Monitor ($299),
                Gaming Laptop ($999)
Clothing        T-Shirt ($20), Jeans ($50), Jacket ($120),
                Winter Coat ($200)
Books           Paperback ($10), Hardcover ($25), Collectors
                Edition ($75)

-- Aggregate tags with custom ordering 

SELECT post_id, STRING_AGG(tag, '|' WITHIN GROUP (ORDER BY tag)) AS tags FROM post_tags GROUP BY post_id;



-- Output:
post_id     tags
-------     ------------------------------------------
P001        coding|javascript|programming|web
P002        design|interface|ui|ux
P003        database|mysql|optimization|sql
P004        cloud|deployment|docker|kubernetes
P005        algorithm|data-structure|leetcode|python

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

Related Functions:

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

MAX()

Returns the largest value from a specified column or expression, ignoring NULL values.

Returns the largest value from a specified column or expression, ignoring NULL values.

Returns the largest value from a specified column or expression, ignoring NULL values.

Returns the largest value from a specified column or expression, ignoring NULL values.

MIN()

Returns the smallest value from a specified column or expression, ignoring NULL values.

Returns the smallest value from a specified column or expression, ignoring NULL values.

Returns the smallest value from a specified column or expression, ignoring NULL values.

Returns the smallest value from a specified column or expression, ignoring NULL values.

AVG()

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

COUNT()

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

One place for all your queries,
directly on your SQL editor