How Can I Dynamically Customize PIVOT Queries in BigQuery?

Query Customization with PIVOT Function in BigQuery

The recently introduced PIVOT function in BigQuery allows for convenient data reshaping, but sometimes we might not know the specific column values beforehand. This article addresses a common challenge faced when trying to pivot data dynamically.

Static PIVOT Query Issues

When we have predefined column values, we can use a straightforward PIVOT query like this:

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

Dynamic PIVOT Query Solution

However, in real-world scenarios, we often don't have the column values available upfront. To handle this, we can dynamically build the PIVOT columns using the following query:

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) 
);

By dynamically generating the PIVOT columns, we can query and reshape data regardless of known column values. This approach proves particularly useful when working with large or evolving datasets.

The above is the detailed content of How Can I Dynamically Customize PIVOT Queries in BigQuery?. For more information, please follow other related articles on the PHP Chinese website!