Turn rows into columns: PIVOT and UNPIVOT

Reports often need values side by side, like a spreadsheet pivot table. PIVOT does that in the FROM clause, and UNPIVOT turns columns back into rows.

How it works

PIVOT groups for you, inside its own clause, so you write no GROUP BY for it, though you can still put one in a query around it. It groups its input by every column it does not use: the column after FOR and the column inside the aggregate are used, and all the others are group keys. It then runs the aggregate once for each value in the IN list, and each result becomes a column. UNPIVOT goes the other way: each column in its IN list becomes a row, and the column name goes into a new column.

Common mistake

A stray column in the input of PIVOT splits every row. If the subquery also selects the fuel type, each make comes back once for each fuel type, and every number is still correct, so nothing looks wrong. Select only the row key, the FOR column and the value. In UNPIVOT, a cell that is NULL disappears from the result.

Worked example

SELECT *
FROM (
  SELECT test_result, EXTRACT(MONTH FROM completed_date) AS month
  FROM `learn.mot.fact_mot_test`
  WHERE completed_date BETWEEN '2024-01-01' AND '2024-03-31'
)
PIVOT(COUNT(*) FOR month IN (1 AS jan, 2 AS feb, 3 AS mar))

The subquery selects only test_result and month. Every month becomes a column, and test_result is the only group key, so the result has two rows: FAILED and PASSED.

Without AS jan, BigQuery would name the columns _1, _2 and _3. Each cell is the number of tests with that result in that month.

All BigQuery lessons