Convert rows to columns with PIVOT in BigQuery
One of BigQuery’s killer functions is the PIVOT operator. It allows you to rotate rows of a table into columns, and aggregate the data to create summary statistics.
Here is an example of how to use BigQuery PIVOT function in a query:
SELECT * FROM ( SELECT year, product, revenue FROM sales ) PIVOT ( SUM(revenue) AS revenue FOR product IN ('tshirts', 'hats', 'socks') )
In this example, the PIVOT operator is used to rotate the product column into three columns (
socks), and aggregate the data by taking the sum of the
revenue column. This produces a table with one row per year, and three columns for each product, showing the total revenue for each product per year.
Unfortunately, one strong limitation is that you must specify the pivot columns names explicitely, you cannot generate them dynamically for now.