How Can I Unpivot Tables in BigQuery to Transform Rows into Columns?

Unpivoting Tables in BigQuery

Restructuring a table to transform rows into columns is a common data manipulation task. In BigQuery, this process is known as unpivoting.

Problem Statement:

Suppose you have a table with data organized in a format similar to the following:

However, your desired output table should be structured like this:

Solution:

To achieve this transformation, you can utilize the UNPIVOT operator in BigQuery. This operator rotates rows into columns, allowing you to reshape your table as required.

Updated Solution (2021):

BigQuery has introduced a dedicated UNPIVOT operator to simplify this process. Using UNPIVOT can be represented as follows:

SELECT
  product,
  sales,
  quarter
FROM (
  SELECT
    product,
    UNNEST([Q1, Q2, Q3, Q4]) AS sales,
    UNNEST(["Q1", "Q2", "Q3", "Q4"]) AS quarter
  FROM UNNEST(sales_table) AS sales_table
)

This query unpivots the columns Q1, Q2, Q3, Q4 into new columns sales and quarter. The UNNEST operator is used to extract the values from the nested array field sales_table.

The above is the detailed content of How Can I Unpivot Tables in BigQuery to Transform Rows into Columns?. For more information, please follow other related articles on the PHP Chinese website!