PIVOT in BigQuery

Pivot in BigQuery


Pivoting data is a helpful process of any analysis. Recently BigQuery announced a new PIVOT operator that will make this easier than ever.

PIVOT(<AGG_FN>(<AGG_COL>) FOR <PIVOT_COL> IN (<val1> [as alias], <val2> [as alias], ... <valn> [as alias]) [as alias]


  • anything in brackets is opional
  • <COLS> is the list of columns returned in the query
  • <TABLE> is the table you a pivoting
    • may not produce a value able or be a subquery using SELECT AS STRUCT
  • <AGG_FN> is an aggregate function that aggregates all input rows across the values of the <PIVOT_COL>
    • may reference columns in <TABLE> and correlated columns, but none defined by the PIVOT clause itself
    • must have only one argument
    • Except for COUNT, you can only use aggregate functions that ignore NULL inputs
    • if using COUNT, you can use * as an argument
  • <AGG_COL> is the column that will be aggregated across the values of <PIVOT_COL>
  • <PIVOT_COL> is the column that will have its values pivoted
    • values must be valid column names (so no spaces or special characters)
    • use the aliases to ensure they are the right format


In this case we’ll see how many matches some popular tennis players have won in the Grand Slams.

Other links

1 Like