Is there a melt command in Snowflake?

user_15629542 0
Viewed 435

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.

melt snowflake-cloud-data-platform unpivot sql
1 Answers

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
user_50708790
Related