SQL Server STRING_AGG in Snowflake, Databricks and BigQuery
BigQuery and Databricks both have STRING_AGG; Snowflake calls it LISTAGG. Ordering syntax differs: SQL Server and Snowflake use WITHIN GROUP (ORDER BY ...), BigQuery puts ORDER BY inside the parentheses.
SQL Server function: STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY ...)] · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | LISTAGG([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Ignores NULL values. The default delimiter is an empty string. | Snowflake docs |
| Databricks | Same name | string_agg([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Databricks SQL and Databricks Runtime 16.4 and later; alias of listagg. On older runtimes use array_join(collect_list(expr), delimiter), which does not guarantee order. | Databricks docs |
| BigQuery | Same name | STRING_AGG([DISTINCT] expr [, delimiter] [ORDER BY ...] [LIMIT n]) | ORDER BY goes inside the parentheses. The default delimiter is a comma. | BigQuery docs |
Behavior differences to check
- SQL Server returns an error when the result exceeds 8,000 bytes for VARCHAR input unless the input is cast to VARCHAR(MAX). The targets have their own size limits but no such cast is needed.
Snowflake
- The separator is optional in the targets and defaults to an empty string, while SQL Server requires it. Keep it explicit to avoid values running together.
Databricks
- The separator is optional in the targets and defaults to an empty string, while SQL Server requires it. Keep it explicit to avoid values running together.
BigQuery
- WITHIN GROUP is not accepted; move the ORDER BY inside STRING_AGG(...).
Examples
Products of each order, ordered by name
SELECT order_id, STRING_AGG(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, ', ') 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;