BigQuery STRING_AGG: How to Combine Grouped Rows Into One String

BigQuery STRING_AGG concatenates the values from many rows into a single string, one string per group, joined by a delimiter you choose. The shape is STRING_AGG(expression, delimiter), and the default delimiter when you leave it out is a comma. It is the fast way to turn a column of rows into a readable list, for example every product a customer bought collapsed into one cell. Below is the syntax, how to order and deduplicate the values, custom delimiters, the LIMIT clause, and how STRING_AGG compares to ARRAY_AGG.

STRING_AGG syntax

STRING_AGG is an aggregate function, so it collapses the rows in each GROUP BY bucket into one value. Give it the column to concatenate and, optionally, the separator to put between values.

SELECT
  customer_id,
  STRING_AGG(product_name, ', ') AS products
FROM sales.orders
GROUP BY customer_id

For a customer with three orders this returns one row and one string, like Keyboard, Monitor, Mouse. STRING_AGG skips NULL values automatically, so a missing product name does not leave a stray delimiter or a NULL in the output. If a group has no non-null values at all, the result is NULL.

Order the values with ORDER BY

By default the order of the concatenated values is not guaranteed. If the list should read in a sensible order, put an ORDER BY inside the function.

SELECT
  customer_id,
  STRING_AGG(product_name, ', ' ORDER BY order_date) AS products_in_order
FROM sales.orders
GROUP BY customer_id

This lists each customer's products oldest to newest. The ORDER BY lives inside the parentheses, next to the aggregated expression, not in the outer query. You can order by any column in the group, not only the one you are aggregating.

Deduplicate with DISTINCT

Add DISTINCT to drop repeated values before they are joined, so a customer who bought the same item twice sees it once.

SELECT
  customer_id,
  STRING_AGG(DISTINCT product_name, ', ' ORDER BY product_name) AS unique_products
FROM sales.orders
GROUP BY customer_id

One rule to know: when you use DISTINCT, any ORDER BY inside the same STRING_AGG has to sort by the aggregated expression itself. You cannot say STRING_AGG(DISTINCT product_name ORDER BY order_date), because ordering by a column that DISTINCT has collapsed is ambiguous. Order by product_name instead, as above.

Custom delimiters and LIMIT

The delimiter is any string literal, so you are not limited to commas. A newline ('\n') builds a stacked list, and a pipe or semicolon reads well in exports. You can also cap how many values go into the string with LIMIT.

SELECT
  ticker_group,
  STRING_AGG(ticker, ' | ' ORDER BY weight DESC LIMIT 5) AS top_holdings
FROM portfolios.positions
GROUP BY ticker_group

That keeps only the five highest-weighted tickers per group, which is the sort of rollup you would build to describe a custom weighted basket in a single line. LIMIT inside an aggregate is a BigQuery convenience worth remembering; it saves a subquery when you only want the top few.

STRING_AGG vs ARRAY_AGG

STRING_AGG and ARRAY_AGG do the same collapsing job but hand back different types, and the right one depends on what happens next.

If the output is going straight to a person, a spreadsheet cell or a dashboard label, STRING_AGG is what you want. If a later step needs to loop over the values or feed them into another function, ARRAY_AGG keeps them as structured data. One handles NULLs the same way (both skip them by default), and both accept the same ORDER BY, DISTINCT and LIMIT clauses inside the parentheses.

Ask it in plain English instead

Remembering that ORDER BY goes inside the parentheses, that DISTINCT constrains the sort, and that STRING_AGG skips NULLs is the kind of detail that slows down ad-hoc queries. Agentsql connects read-only to BigQuery, turns a question like "list each customer's products in one row" into the correct Standard SQL, runs it, and shows you the query so you can check the ordering. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask BigQuery a question in plain English.