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.
|> UNION ALL (SELECT ...)takes the pipe as its left side and the query in parentheses as its right side.ALLkeeps every row andDISTINCTremoves duplicate rows. BigQuery needs one of the two words after the keyword.|> INTERSECTand|> EXCEPTneedDISTINCT, because BigQuery has noALLform of them.- The two sides are matched by column position, and the result keeps the names of the pipe. Both sides need the same number of columns, and each pair needs types that fit. Two columns of the same type in the wrong order are not an error: the values land in the wrong columns.
- A
|> UNIONis one step of the chain. The steps after it see the rows of both sides, so a|> ORDER BYafter it sorts them together. |> PIVOT(SUM(amount) FOR quarter IN (1 AS q1, 2 AS q2))uses the columnsquarterandamount. Every other column in the pipe is a group key, and the result has one row for each combination of their values.|> UNPIVOT(amount FOR quarter IN (q1, q2))makes a row for each listed column.quarterholds the column name,amountholds its value, and the columns you do not list stay as they are.UNPIVOTleaves out a row whose value isNULL.UNPIVOT INCLUDE NULLSkeeps it.
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 regionThe 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.