Redshift LISTAGG in Snowflake, Databricks and BigQuery
Snowflake keeps LISTAGG with the same WITHIN GROUP syntax, and Databricks 16.4+ has listagg too. BigQuery uses STRING_AGG with ORDER BY inside the parentheses. Redshift fails when the result exceeds VARCHAR(MAX), so long lists that never worked there will start working.
Redshift function: LISTAGG([DISTINCT] aggregate_expression [, 'delimiter']) [WITHIN GROUP (ORDER BY order_list)] · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | LISTAGG([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Ignores NULL values; default delimiter is an empty string. | Snowflake docs |
| Databricks | Same name | listagg([ALL | DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Databricks SQL and Databricks Runtime 16.4 and later; older runtimes use array_join(collect_list(expr), delimiter). | Databricks docs |
| BigQuery | Different name | STRING_AGG([DISTINCT] expr [, delimiter] [ORDER BY ...]) | ORDER BY goes inside the parentheses; default delimiter is a comma. | BigQuery docs |
Behavior differences to check
- Redshift's DISTINCT ignores trailing spaces ('a' and 'a ' count as one value); the targets treat them as different values.
BigQuery
- WITHIN GROUP is not accepted; move the ORDER BY inside STRING_AGG(...).
Examples
Products of each order
SELECT order_id, LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) AS products FROM order_items GROUP BY order_id;SELECT order_id, LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) AS products FROM order_items GROUP BY order_id;SELECT order_id, listagg(product, ', ') WITHIN GROUP (ORDER BY product) AS products FROM order_items GROUP BY order_id;SELECT order_id, STRING_AGG(product, ', ' ORDER BY product) AS products FROM order_items GROUP BY order_id;