BigQuery PIVOT: How to Turn Rows Into Columns

BigQuery PIVOT rotates the values in a column into separate columns of their own, so a "quarter" column with the values Q1 through Q4 becomes four columns named Q1, Q2, Q3 and Q4. The syntax is SELECT ... FROM table PIVOT(aggregate FOR pivot_column IN (value1, value2, ...)). It is the clean replacement for a stack of SUM(CASE WHEN ...) expressions. The one rule to remember is that you must list the output columns explicitly, because SQL needs to know the result shape before it runs. Below is the syntax, the multi-column pattern, and how to handle values you do not know in advance.

PIVOT syntax and a first example

Say you have one row per order with a quarter column and a revenue column, and you want quarters across the top with total revenue in each.

SELECT *
FROM (
  SELECT region, quarter, revenue
  FROM sales.orders
)
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
)

You get one row per region, with four revenue columns. The inner subquery matters: only the columns you select into it take part. Any column you leave in but do not aggregate or pivot becomes an implicit grouping key, which is a common source of "why do I have more rows than I expected" surprises. Select exactly the pivot column, the value to aggregate, and the columns you want as row keys, and nothing else.

Multiple aggregations and cleaner column names

You can pivot more than one aggregate at once, and you can alias both the values and the aggregates to control the final column names.

SELECT *
FROM (SELECT region, quarter, revenue, orders FROM sales.summary)
PIVOT (
  SUM(revenue) AS rev,
  SUM(orders) AS cnt
  FOR quarter IN ('Q1' AS q1, 'Q2' AS q2)
)

That produces columns named rev_q1, cnt_q1, rev_q2 and cnt_q2. Aliasing the IN values keeps the headers tidy and, more importantly, lets you pivot on values that are not valid column names on their own, like numbers or strings with spaces.

The dynamic columns catch

PIVOT cannot discover its output columns for you. If next quarter a Q5 appears in the data, the query above simply ignores it, because Q5 is not in the IN list. BigQuery has no built-in "pivot on whatever values exist" mode in a plain query. There are two honest ways around it.

• List the values you care about and accept that new values need a query edit. For fixed sets like quarters, weekday names, or a known set of statuses, this is fine and keeps the query readable.
• Build the SQL as a string and run it with EXECUTE IMMEDIATE. You first query the distinct values, assemble the IN list into a string, then execute the generated statement. It works, but you are now maintaining SQL that writes SQL, so reserve it for cases where the value set genuinely changes often.

Most of the time the fixed list is the right call. Dynamic pivots are one of those features that sound essential and turn out to be needed far less often than people expect.

PIVOT vs CASE WHEN

Before PIVOT existed, everyone rotated rows with conditional aggregation. That still works and is worth knowing, because it is more flexible when the logic is irregular.

The equivalent CASE version of the first example is SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1 repeated per quarter. See BigQuery CASE WHEN for that pattern in full. Reach for CASE when each column needs a different condition, and PIVOT when it is the same aggregate across a clean set of values.

UNPIVOT: the other direction

The reverse operation, UNPIVOT, folds several columns back into rows, which is what you want before feeding a wide report into a charting tool that expects long-format data. FROM table UNPIVOT(revenue FOR quarter IN (q1, q2, q3, q4)) turns those four columns back into a quarter column and a revenue column.

Turn the result into something you can share

A pivoted summary is usually the last SQL step before a human looks at it. Once you have the cross-tab, dropping it into a board-ready deck is often the next task, and long-format UNPIVOTed data is what most chart libraries want underneath. Either way, get the aggregation right in the warehouse first so the numbers are trustworthy before they leave it.

Ask it in plain English instead

You do not have to remember PIVOT syntax to get a cross-tab. Agentsql connects read-only to BigQuery, turns a plain-English question like "revenue by region across the four quarters" into the correct Standard SQL, runs it, and shows you the query so you can verify the pivot. See the BigQuery integration for how it connects and the charts and dashboards feature for how it renders the result, then see how it works and ask BigQuery a question in plain English.