How to Use LISTAGG in Snowflake

What is LISTAGG in Snowflake?

The LISTAGG function in Snowflake allows you to aggregate and concatenate multiple text values into a single string. This is useful for creating summaries, lists, or grouped textual data.

Syntax for LISTAGG in Snowflake

LISTAGG(expression [, delimiter]) [WITHIN GROUP (ORDER BY expression)]

• expression: The column or expression to concatenate.
• delimiter: Optional separator used between values (default is a comma).
• ORDER BY: Optional clause to order aggregated values.

Examples

Basic LISTAGG Example

Here’s how to concatenate city names within each region:

SELECT 
  region, 
  LISTAGG(city, ', ') AS cities
FROM locations
GROUP BY region;

Output:

Custom Delimiter

Use the LISTAGG(expression, delimiter) syntax to specify a custom delimiter:

SELECT 
  region, 
  LISTAGG(city, '; ') AS cities
FROM locations
GROUP BY region;

Output:

LISTAGG with ORDER BY

Use the WITHIN GROUP (ORDER BY ...) clause to specify the order of concatenated values:

SELECT 
  region, 
  LISTAGG(city, ', ') WITHIN GROUP (ORDER BY city ASC) AS cities_sorted
FROM locations
GROUP BY region;

This ensures that cities are ordered alphabetically within each concatenation.

Distinct Aggregation

If you need only unique values:

SELECT 
  department, 
  LISTAGG(DISTINCT employee_name, ', ') AS employees
FROM employee_data
GROUP BY department;

Output:

Conclusion

• LISTAGG in Snowflake is powerful for concatenating textual values.
• ORDER BY within LISTAGG allows you to control the sequence of concatenated items.
• DISTINCT within LISTAGG allows you to aggregate unique values.
• The delimiter option within LISTAGG allows you to specify a custom delimiter.

For more details, refer to Snowflake’s official documentation.