How Can I Dynamically Pivot Data in BigQuery Without Knowing the Column Values in Advance?

Dynamically Using PIVOT in BigQuery

The new PIVOT function in BigQuery allows users to transform data by summarizing multiple values for a group into separate columns.

Issue:

In real-world scenarios, the quarter values may not be known in advance. Running a static PIVOT query with hard-coded quarter values becomes infeasible.

Solution:

To dynamically handle unknown quarter values, the following approach can be used:

1. Create a Subquery to Get Distinct Quarter Values:

(SELECT DISTINCT quarter FROM `project.dataset.Produce` ORDER BY quarter)

2. Use STRING_AGG to Generate a Dynamic Pivot Column List:

STRING_AGG(quarter, '", "')

3. Execute Immediate to Build Pivot Query Dynamically:

EXECUTE IMMEDIATE (
  'SELECT * FROM (SELECT * FROM `project.dataset.Produce`)
  PIVOT(SUM(sales) FOR quarter IN (' || STRING_AGG(quarter, '", "') || '"))'
)

By dynamically building the pivot column list based on distinct quarter values, this method allows for flexible PIVOT operations without the need to pre-determine the quarter range.

The above is the detailed content of How Can I Dynamically Pivot Data in BigQuery Without Knowing the Column Values in Advance?. For more information, please follow other related articles on the PHP Chinese website!