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. Snowflake has added the UNPIVOT query construct to help.
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(
    select 'a' as A, 1 as B, 2 as C union all 
    select 'b' as A, 3 as B, 4 as C union all
    select '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