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!
2020 update: fhoffa.x.unpivot()
See:
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]
)