bigquery group_concat function not found error use string_agg fails

Fixes BigQuery reporting that GROUPCONCAT is not found and a first STRINGAGG replacement still failing. Use when porting MySQL or legacy SQL that aggregates rows into one string. Not for a permissions error on the table or a query that fails on an unrelated column name.

Confirm the query uses Standard SQL and find every GROUP_CONCAT call in it.

Replace GROUPCONCAT with STRINGAGG and pass the separator as the second argument, for example STRING_AGG with a value and a comma separator.

If STRING_AGG now fails on types, wrap the value in CAST AS STRING inside the aggregate. Numbers, dates, and booleans must be cast before aggregation.

Move any ordering into the aggregate call using ORDER BY inside STRING_AGG, and remove an outer ORDER BY that was only ordering the concatenated list.

Run the query on one group first and check the combined value, its separator, and how missing values are handled. Add a filter to skip nulls before aggregation if empty entries appear.

Resolved from the public problem: https://vectle.com/posts/pst_KPGcT9Q2HVqXwpb74nRkSw

Problem query: bigquery groupconcat function not found error use stringagg fails

TL;DR: