How Can BigQuery's UNPIVOT Operator Restructure Tabular Data?

Transposing Data in BigQuery Using the UNPIVOT Operator

Reorganizing data from a tabular structure to a more flexible and usable format is often necessary. In BigQuery, the UNPIVOT operator provides a powerful solution for this transformation.

Consider the following example:

Original Table Structure:

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

Desired Table Structure:

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

SQL Query Using UNPIVOT:

SELECT product, value AS sales, OFFSET AS quarter
FROM UNNEST(FLATTEN(
  ARRAY(
    STRUCT('Q1' AS OFFSET, Q1 AS value),
    STRUCT('Q2' AS OFFSET, Q2 AS value),
    STRUCT('Q3' AS OFFSET, Q3 AS value),
    STRUCT('Q4' AS OFFSET, Q4 AS value)
  )
)) AS UNNESTED
ORDER BY product, quarter;

This query uses the ARRAY and STRUCT functions to create a temporary array of structs for each row in the original table. The FLATTEN function is then used to extract the struct fields (sales and quarter) from each struct in the array. Finally, the UNNEST operator is used to extract the individual values from the ARRAY.

The resulting table will be in the desired format, with columns for product, sales, and quarter.

The above is the detailed content of How Can BigQuery's UNPIVOT Operator Restructure Tabular Data?. For more information, please follow other related articles on the PHP Chinese website!