How to Unpivot in Bigquery

How to unpivot in BigQuery?

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]
)

How to Unpivot a Struct in BigQuery?

You cannot unpivot columns with different data types (in your example those are INT64 and DATE). So consider below approach

SELECT * FROM (
  SELECT Document_ID, User_Activity.* 
  FROM `business-analytics-workbench.RAW.User_Activity` as UA
)
UNPIVOT(Activity FOR PERIOD in (Last_7_Days,Last_14_Days,Last_30_Days,Last_90_Days))    

If applied to sample data in y our question output is

how to dynamically unpivot only those columns with a specific suffix in bigquery

Additionally, to @Mikhail Answer that is correct you need to add a WHERE clause with a REGEXP_CONTAINS expression as the following one:

where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')

The full Query will be:

select * from your_table
unpivot (metric for col in (product_next, upload_last, active_next)) 
where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')  

How to unpivot multiple colums to rows in Bigquery sql

I found the solution:

SELECT month, metric, value
  FROM final 
  UNPIVOT(value FOR metric IN (mail_sent, mail_received, mail_opened))
  ORDER BY 1

Unpivot to create multple columns

Another option without UNPIVOT

SELECT match, pp.* 
  FROM sample, UNNEST([
    STRUCT(player1 AS player, position1 AS position),
          (player2, position2),
          (player3, position3)
  ]) pp;

output will be:

How Unpivot / Transpose some Columns in SQL BigQuery

You were on the right track with the unpivot functionality, see the code below:

select advertiser, conversion, sum(total_conversions)
from sample_data
UNPIVOT (total_conversions for conversion in (conversion_1, conversion_2, conversion_3, conversion_4))
group by advertiser, conversion

with your data this results in:

---

Related Topics