Unpivot (Melt)

Unpivot / melt

Explore this snippet with some demo data here.

Description

The act of unpivoting (or melting, if you’re a pandas user) is to convert columns to rows. BigQuery has recently added the UNPIVOT operator as a preview feature.
The query template is

SELECT
  <columns_to_keep>,
  metric,
  value
FROM <table> UNPIVOT(value FOR metric IN (<columns_to_unpivot>))
order by metric, value

where

  • columns_to_keep - all the columns to be preserved
  • columns_to_unpivot - all the columns to be converted to rows
  • table - the table to pull the columns from

(The output column names ‘metric’ and ‘value’ can also be changed.)

Examples

In the examples below, we use a table with columns A, B, C. We take the columns B and C, and turn their values into columns called metric (containing either the string ‘B’ or ‘C’) and value (the value from either the column B or C). The column A is preserved.

-- Define some dummy data
with a as (
  select * from unnest([
    struct( 'a' as A, 1 as B, 2 as C ),
    struct( 'b' as A, 3 as B, 4 as C ),
    struct( 'c' as A, 5 as B, 6 as C )
  ])
)
SELECT
  A,
  metric,
  value
FROM a UNPIVOT(value FOR metric IN (B, C))
order by metric, value
A metric value
a B 1
b B 3
c B 5
a C 2
b C 4
c C 6

If you would rather not use preview features in BigQuery, there is another generic solution (from StackOverflow), though it may be much less performant than the built-in UNPIVOT implementation.
The query template here is

select
  <columns_to_keep>,
  metric,
  value
from (
  SELECT
    *,
    REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') metric, 
    REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(1)], r'^"|"$', '') value 
  FROM
    <table>,
    UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(<table>), r'{|}', ''))) pair
)
where metric not in (<column_names_to_keep>)
order by metric, value

where

  • column_names_to_keep - this is a list of the JSON-escaped names of the columns to keep

An annotated example is: