The new PIVOT function in BigQuery

Question

Today BigQuery released a new cool function called PIVOT.

Se below how it works:

with Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))


+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple   | 77 | 0  | 25 | 2  |
| Kale    | 51 | 23 | 45 | 3  |
+---------+----+----+----+----+

My question is, in the real world, we don't know in advance the quarter values.

I tried to do It more dynamically with:

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter in (select distinct quarter from Produce))

Without success. Any clue on how to handle that?

Here is a link to reddit cross-post

Answer 1

Use below - it dynamically builds pivot columns

execute immediate (             
select '''select * from (select * from `project.dataset.Produce`)
  pivot(sum(sales) for quarter in ("''' ||  string_agg(quarter, '", "')  || '''"))
'''
from (select distinct quarter from `project.dataset.Produce` order by quarter) 
);