listagg is an aggregation function in Snowflake which concatenates values, in its simplest form all you need to run is
select listagg(col_1) from table. In this post I will walk through what it’s useful for and how to use it in your database.
Snowflake listagg, what does it do?
listagg is a column aggregation function which concatenates all values within a column to a list, using a defined delimiter to separate the elements. It’s often used in conjunction with a
group by clause to aggregate up to a given level.
This sounds similar to the concat function, but differs as the concat function concatenates along row wise, whilst listagg concatenates up column wise to a list.
listagg with a delimiter
The most common syntax for using
listagg in Snowflake is:
listagg( column_1, delimiter ) select listagg( product_name, "," ) from products
This basic syntax will concatenate all of the values in
product_name and separate them all by using the
delimiter which in our case is
listagg with distinct values
It’s also possible to return a concatenated list containing just the distinct values in a column. This is done using the following syntax:
listagg( distinct column_1, delimiter ) select listagg( distinct category_name, "|" ) from products
listagg within group
We can take this function one step further by adding the
within group function, which will concatenate the values in a given order:
listagg( column1, delimiter) within group (order by column_2 desc) select category_name, listagg( product_name, ",") within group (order by price desc) from products group by category_name
Limitations and common errors when using listagg
There are a few things that you should be aware of when using this function:
- The delimiter must be of datatype
- If no delimiter is provided then an empty string is used
- There isn’t a defined order of values within the list, unless an
order byclause is used in conjunction with
nullvalues are not included within the output list
listaggis an aggregation function, so other columns included in the statement need to either be the subject of the aggregation or aggregated themselves.
Join the newsletter to receive the latest updates from the world of Data Science in your inbox.