sqlsnowflake-cloud-data-platformunpivotmelt

Is there a melt command in Snowflake?

Is there a Snowflake command that will transform a table like this:

a,b,c
1,10,0.1
2,11,0.12
3,12,0.13

to a table like this:

key,value
a,1
a,2
a,3
b,10
b,11
b,13
c,0.1
c,0.12
c,0.13

?

This operation is often called melt in other tabular systems, but the basic idea is to convert the table into a list of key value pairs.

There is an UNPIVOT in SnowSQL, but as I understand it UNPIVOT requires to manually specify every single column. This doesn't seem practical for a large number of columns.

Solution

  • Snowflake's SQL is powerful enough to perform such operation without help of third-party tools or other extensions.

    Data prep:

    CREATE OR REPLACE TABLE t(a INT, b INT, c DECIMAL(10,2))
AS
SELECT 1,10,0.1
UNION SELECT 2,11,0.12
UNION SELECT 3,12,0.13;

    enter image description here

    Query(aka "dynamic" UNPIVOT):

    SELECT f.KEY, f.VALUE
FROM (SELECT OBJECT_CONSTRUCT_KEEP_NULL(*) AS j FROM t) AS s
,TABLE(FLATTEN(input => s.j)) f
ORDER BY f.KEY;

    Output:

    enter image description here

    How does it work?

    1. Transform row into JSON(row 1 becomes { "A": 1,"B": 10,"C": 0.1 })
    2. Parse the JSON into key-value pairs using FLATTEN