How To Pivot In Google Bigquery

Suppose I have the following query sent to BQ: SELECT shipmentID, category, quantity FROM [myDataset.myTable] Further, suppose that the query returns data such as: shipmentID cat

Solution 1:

This is a way to do:

select shipmentID,
  sum(IF (category='shoes', quantity, 0)) AS shoes,
  sum(IF (category='hats', quantity, 0)) AS hats,
  sum(IF (category='shirts', quantity, 0)) AS shirts,
  sum(IF (category='toys', quantity, 0)) AS toys,
  sum(IF (category='books', quantity, 0)) AS books,
from
  (select1as shipmentID,           'shoes'as category,    5as quantity),
  (select1as shipmentID,           'hats'as category,      3as quantity),
  (select2as shipmentID,           'shirts'as category,    1as quantity),
  (select2as shipmentID,           'hats'as category,      2as quantity),
  (select3as shipmentID,           'toys'as category,      3as quantity),
  (select2as shipmentID,           'books'as category,     1as quantity),
  (select3as shipmentID,           'shirts'as category,    1as quantity),
groupby shipmentID

This returns:

+-----+------------+-------+------+--------+------+-------+---+|Row| shipmentID | shoes | hats | shirts | toys | books ||+-----+------------+-------+------+--------+------+-------+---+|1|1|5|3|0|0|0|||2|2|0|2|1|0|1|||3|3|0|0|1|3|0||+-----+------------+-------+------+--------+------+-------+---+

See the manual for other pivot table example.