title: The new PIVOT function in BigQuery
body: <p>Today BigQuery released a new cool function called <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator" rel="noreferrer">PIVOT</a>.</p>
  <p>Se below how it works:</p>
  <pre><code>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  |
  +---------+----+----+----+----+
  </code></pre>
  <p>My question is, in the real world, we don't know in advance the quarter values.</p>
  <p>I tried to do It more dynamically with:</p>
  <pre><code>SELECT * FROM
    (SELECT * FROM Produce)
    PIVOT(SUM(sales) FOR quarter in (select distinct quarter from Produce))
  </code></pre>
  <p>Without success. Any clue on how to handle that?</p>
  <p>Here is a <a href="https://www.reddit.com/r/bigquery/comments/n9lft0/the_new_pivot_function_in_bigquery/" rel="noreferrer">link</a> to reddit cross-post</p>