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 (tshirts, hats and 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.