How to unpivot in BigQuery?

Question

Not sure what functions to call, but transpose is the closest thing I can think of.

I have a table in BigQuery that is configured like this:

but I want to query a table that is configured like this:

What does the SQL code look like for creating this table?

Thanks!

Answer 1

Update 2021:

A new UNPIVOT operator has been introduced into BigQuery.

Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:

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

After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:

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

Query:

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2
)
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

Answer 2

2020 update: fhoffa.x.unpivot()

See:

• https://medium.com/@hoffa/how-to-unpivot-multiple-columns-into-tidy-pairs-with-sql-and-bigquery-d9d0e74ce675

I created a public persistent UDF. If you have a table a, you can give the whole row to the UDF for it to be unpivotted:

SELECT geo_type, region, transportation_type, unpivotted
FROM `fh-bigquery.public_dump.applemobilitytrends_20200414` a
  , UNNEST(fhoffa.x.unpivot(a, '_2020')) unpivotted

It transforms a table like this:

Into this

---

As a comment mentions, my solution above doesn't solve for the question problem.

So here's a variation, while I look how to integrate all into one:

CREATE TEMP FUNCTION unpivot(x ANY TYPE) AS (
(
  SELECT 
   ARRAY_AGG(STRUCT(
     REGEXP_EXTRACT(y, '[^"]+') AS key
   , REGEXP_EXTRACT(y, ':([0-9]+)') AS value
   ))
  FROM UNNEST((
    SELECT REGEXP_EXTRACT_ALL(json,'"[smlx][meaxl]'||r'[^:]+:\"?[^"]+?') arr
    FROM (SELECT TO_JSON_STRING(x) json))) y
)
);

SELECT location, unpivotted.*
FROM `robotic-charmer-726.bl_test_data.reconfiguring_a_table` x
  , UNNEST(unpivot(x)) unpivotted

---

Previous answer:

Use the UNION of tables (with ',' in BigQuery), plus some column aliasing:

SELECT Location, Size, Quantity
FROM (
  SELECT Location, 'Small' as Size, Small as Quantity FROM [table]
), (
  SELECT Location, 'Medium' as Size, Medium as Quantity FROM [table]
), (
  SELECT Location, 'Large' as Size, Large as Quantity FROM [table]
)

Answer 3

@Felipe, I tried this using standard-sql but I get an error on the first line of your query that says: "Column name Location is ambiguous at [1:8]"

I've used an alternate query that works for me:

SELECT Location, 'Small' as Size, Small as Quantity FROM `table`
UNION ALL
SELECT Location, 'Medium' as Size, Medium as Quantity FROM `table`
UNION ALL
SELECT Location, 'Large' as Size, Large as Quantity FROM `table`

Answer 4

I have a solution that uses STRUCTs, ARRAYs and CROSS JOIN + UNNEST:

WITH
  My_Table_Metrics_Data AS (
  SELECT
    ...,
    [
        STRUCT('...' AS Metric, ... AS Data),
        STRUCT('...' AS Metric, ... AS Data),
    ] AS Metrics_Data
  FROM
    `My_Dataset.My_Table`
  WHERE
    ...
  )
SELECT
  ...,
  Metric_Data
FROM
  My_Table_Metrics_Data
CROSS JOIN
  UNNEST(My_Table_Metrics_Data.Metrics_Data) AS Metric_Data

Full explanation and instructions: https://yuhuisdatascienceblog.blogspot.com/2018/06/how-to-unpivot-table-in-bigquery.html