Explore this snippet with some demo data here.
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
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.)
In the examples below, we use a table with columns
C. We take the columns
C, and turn their values into columns called
metric (containing either the string ‘B’ or ‘C’) and
value (the value from either the column
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