How to Unpivot Data in BigQuery?

How to Transform Data from a Pivoted to an Unpivoted Structure in BigQuery

In BigQuery, you're presented with the challenge of transforming a pivoted table into an unpivoted format. This involves transposing the data into a more tabular representation. To achieve this, consider the following steps:

Using the UNPIVOT Operator (Recommended)

BigQuery now offers a dedicated UNPIVOT operator that simplifies this transformation. This operator allows you to specify the pivot columns to rotate (e.g., Q1, Q2, Q3, Q4) and the resulting columns (e.g., sales, quarter).

SQL Syntax:

SELECT product, UNPIVOT(value FOR quarter IN (Q1, Q2, Q3, Q4)) AS sales_quarter
FROM pivoted_table

Using a Nested Query

Prior to the introduction of the UNPIVOT operator, BigQuery users could achieve unpivoting using a nested query:

SQL Syntax:

SELECT product,
  (SELECT value FROM UNNEST(SAFE_CAST(STRUCT(NULL AS name, Q1 AS "value") AS STRUCT<name STRING, value STRING>))) AS Q1,
  (SELECT value FROM UNNEST(SAFE_CAST(STRUCT(NULL AS name, Q2 AS "value") AS STRUCT<name STRING, value STRING>))) AS Q2,
  (SELECT value FROM UNNEST(SAFE_CAST(STRUCT(NULL AS name, Q3 AS "value") AS STRUCT<name STRING, value STRING>))) AS Q3,
  (SELECT value FROM UNNEST(SAFE_CAST(STRUCT(NULL AS name, Q4 AS "value") AS STRUCT<name STRING, value STRING>))) AS Q4
FROM pivoted_table

Example Data Conversion

The following example will convert the pivoted table:

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

Into the unpivoted table:

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

The above is the detailed content of How to Unpivot Data in BigQuery?. For more information, please follow other related articles on the PHP Chinese website!