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.
FROM (SELECT make, test_result FROM ...) PIVOT(COUNT(*) FOR test_result IN ('PASSED', 'FAILED'))returns one row for eachmakewith the columnsmake,PASSEDandFAILED. The column names come from the values.- The
INlist is part of the query text. BigQuery does not read the data to build it. A value in the data that is not in the list is dropped without a warning. A listed value that is not in the data gives a column of0forCOUNTandNULLforSUM. To pivot on whatever values the data has today, build the query as a string and run it withEXECUTE IMMEDIATE. - A value becomes a column name, so it must be a name BigQuery accepts.
'Hybrid Electric (Clean)'is refused because of the brackets. Write'Hybrid Electric (Clean)' AS hybridto choose the name. A number such as1becomes_1, unless you alias it. PIVOTis shorthand for oneCOUNTIForSUM(IF(...))column for each value, with aGROUP BYon the row key. It is shorter when there are many values. A stray column splits the rows of aPIVOT, and aCOUNTIFquery only groups by what you write. In both forms, a value spelled differently from the data counts nothing.UNPIVOT(amount FOR kind IN (col_a, col_b))makes a row for each listed column.kindholds the column name,amountholds its value, and every column you do not list stays as it is. The listed columns must all have the same type.UNPIVOTleaves out every row whose value isNULL. This isEXCLUDE NULLS, the default. WriteUNPIVOT INCLUDE NULLS (...)to keep them.
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.