PIVOT in BigQuery

Pivot in BigQuery

Description

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

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

where:

  • 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

Example

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


Other links

1 Like