Snowflake
Aggregate Values Into Delimited String

Snowflake LISTAGG: Aggregate Multiple Values Into a Delimited String

What it does

LISTAGG concatenates values from a group into a single string with a specified delimiter separating them. It’s like making a list where each item is joined by a comma or another separator.

What you get

A single string that contains all the concatenated non-NULL values. If all values are NULL or the input is empty, the result is an empty string.

Common use cases

  • Combine names, addresses, or any set of values into one easy-to-read string.
  • Summarize data points that belong together, like listing all products purchased in a single transaction.

How to use it

You can concatenate items simply by specifying the column you want to aggregate and choosing a delimiter:


_10
SELECT
_10
LISTAGG (character_name, ', ') WITHIN GROUP (
_10
ORDER BY
_10
character_name
_10
) AS characters
_10
FROM
_10
galactic_events;

This would collect all character_name entries, separate them with a comma followed by a space, and order them alphabetically.

To avoid duplicate entries in your list, use the DISTINCT keyword:


_10
SELECT
_10
LISTAGG (DISTINCT character_name, '; ') WITHIN GROUP (
_10
ORDER BY
_10
character_name
_10
) AS unique_characters
_10
FROM
_10
galactic_events;

You can use LISTAGG both as an aggregate or a window function:

  • as an aggregate function

_10
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]

  • as a window function

_10
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
_10
[ WITHIN GROUP ( <orderby_clause> ) ]
_10
OVER ( [ PARTITION BY <expr2> ] )

where

  • expression: This is the column or expression that you want to concatenate.
  • delimiter: The string that separates entries in the list. If omitted, entries will just be concatenated together without any separation.
  • DISTINCT: Optional keyword to remove duplicate values from the result.
  • ORDER BY: Determines the order of the concatenated results.
  • PARTITION BY: (Only in window function use) Groups rows that receive the same treatment.

Examples

Aggregating related data

Suppose we have a table called galactic_events that records events attended by characters from "The Hitchhiker's Guide to the Galaxy."

Input

Table: galactic_events

idcharacter_nameevent
1Arthur DentVogon Poetry Reading
2Arthur DentMilliways Dinner
3Arthur DentDeep Thought Inquiry
4Ford PrefectVogon Poetry Reading
5Ford PrefectEscape from Vogsphere
6Zaphod BeeblebroxPan Galactic Gargle Blaster Party

Snowflake SQL Query


_10
SELECT
_10
character_name,
_10
LISTAGG (event, ', ') WITHIN GROUP (
_10
ORDER BY
_10
event
_10
) AS events_attended
_10
FROM
_10
galactic_events_dupl
_10
GROUP BY
_10
character_name;

Output

character_nameevents_attended
Arthur DentDeep Thought Inquiry, Milliways Dinner, Vogon Poetry Reading
Ford PrefectEscape from Vogsphere, Vogon Poetry Reading
Zaphod BeeblebroxPan Galactic Gargle Blaster Party
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started