UNION, PIVOT and UNPIVOT as pipe steps

Reports often need a total added under the data, a table turned on its side, or a wide table made long again. UNION, PIVOT and UNPIVOT do each of these in the middle of a chain, with no nested FROM subquery.

How it works

The pipe carries one table from step to step. A UNION step puts the rows of its query under that table. A PIVOT step groups the table by every column it does not use, and makes a column for each listed value. An UNPIVOT step makes a row for each listed column. None of them chooses columns for you: they work on whatever the steps before them left in the table.

Common mistake

A pipe keeps every column until a step removes one. A column left behind by an EXTEND, an AGGREGATE or a join is a group key for a later PIVOT. It splits what should be one row into several, one for each value of the leftover column. After an UNPIVOT, COUNT(*) counts the rows the step made, which is one for each listed column of each original row.

Worked example

WITH plan AS (
  SELECT 'north' AS region, 10 AS q1, 20 AS q2
  UNION ALL SELECT 'south', 5, 8
)
FROM plan
|> UNPIVOT(sales FOR quarter IN (q1, q2))
|> UNION ALL (SELECT 'west', 7, 'q1')
|> PIVOT(SUM(sales) FOR quarter IN ('q1', 'q2'))
|> ORDER BY region

The UNPIVOT makes four rows from the two regions, the UNION ALL adds a row for west, and the PIVOT turns the quarters back into the columns q1 and q2.

west has no q2 row, so its q2 is NULL. The query has no nested FROM subquery: each step reads the table the step before it made.

All BigQuery lessons