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:
_10SELECT_10 LISTAGG (character_name, ', ') WITHIN GROUP (_10 ORDER BY_10 character_name_10 ) AS characters_10FROM_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:
_10SELECT_10 LISTAGG (DISTINCT character_name, '; ') WITHIN GROUP (_10 ORDER BY_10 character_name_10 ) AS unique_characters_10FROM_10 galactic_events;
You can use LISTAGG both as an aggregate or a window function:
- as an aggregate function
_10LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]
- as a window function
_10LISTAGG( [ 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
| id | character_name | event |
|---|---|---|
| 1 | Arthur Dent | Vogon Poetry Reading |
| 2 | Arthur Dent | Milliways Dinner |
| 3 | Arthur Dent | Deep Thought Inquiry |
| 4 | Ford Prefect | Vogon Poetry Reading |
| 5 | Ford Prefect | Escape from Vogsphere |
| 6 | Zaphod Beeblebrox | Pan Galactic Gargle Blaster Party |
Snowflake SQL Query
_10SELECT_10 character_name,_10 LISTAGG (event, ', ') WITHIN GROUP (_10 ORDER BY_10 event_10 ) AS events_attended_10FROM_10 galactic_events_dupl_10GROUP BY_10 character_name;
Output
| character_name | events_attended |
|---|---|
| Arthur Dent | Deep Thought Inquiry, Milliways Dinner, Vogon Poetry Reading |
| Ford Prefect | Escape from Vogsphere, Vogon Poetry Reading |
| Zaphod Beeblebrox | Pan Galactic Gargle Blaster Party |
