Snowflake how can we run an unpivot query over an array of fields instead of explicitly declare each field?

Question

I need to unpivot a table using fields specified in an array. I have the following query:

select
    *
FROM
    TEMP_TABLE_NAME T unpivot (
        val_col for name_col in (
            array_of_fields
        )
    );

The issue is that our data is dynamic, we might receive data having 10 fields, or 100 fields depending on the survey we need to upload into Snowflake.

So instead of explicitly declare fields that I need to use, I exported the fields into an array and I need to use them inside the in clause of the unpivot. The query works fine when we specify some fields.

If you fix your data model so the "variable" columns were in rows and not columns, then you could easily fix this problem. — Gordon Linoff, Mar 01 '21 at 16:20
@GordonLinoff Our data model is based on data vault technique and Microsoft data models for non profit entities thats why we are doing it like so. — alim1990, Mar 01 '21 at 16:49
Is it possible if this was inside a stored procedure and we used JavaScript to extract field from the array into the IN clause ? — alim1990, Mar 01 '21 at 17:14
@alim1990 to answer your question in the comments, yes - that's the way that you'd do it if you had dynamic number of columns to unpivot on. — Simon D, Mar 01 '21 at 18:05
@SimonDarr what if we need to cast everything into varchar as we dont know how many fields we are having and what type is for each one. — alim1990, Mar 01 '21 at 18:18

score 3 · Answer 1 · answered Mar 02 '21 at 00:14

You can get a dynamic unpivot with a JavaScript UDTF:

CREATE OR REPLACE FUNCTION my_unpivot(R OBJECT, A ARRAY)
    RETURNS TABLE (KEY VARCHAR, VALUE VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '
{
    processRow: function f(row, rowWriter, context){
       for (const element of row.A) {
          rowWriter.writeRow({KEY:element, VALUE:row.R[element]});
       }
    }
}';

SELECT empid, dept, x.key month, x.value sales
FROM (
    select *, object_construct(a.*) obj
    from monthly_sales a
), TABLE(my_unpivot(obj, array_construct('JAN', 'FEB', 'MAR', 'APRIL'))) x
;

The sample data for this example:

create or replace table monthly_sales(empid int, dept text, jan int, feb int, mar int, april int);


insert into monthly_sales values
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);

For idea of JS function. Though in this situation it could be rewritten with JSON only. I really hope one day we get [Polymorphic table functions](https://www.iso.org/standard/69776.html) support which is part of SQL standard :) — Lukasz Szozda, Jul 29 '21 at 17:41

Lukasz Szozda · Answer 2 · 2023-04-11T14:54:47.313

Using data provided in @Felipe's answer:

create or replace table monthly_sales(empid int, dept text,
                                      jan int, feb int, mar int, april int);

insert into monthly_sales(empid, dept, jan, feb, mar, april) values
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);

The query could be rewritten as:

SELECT s.empid, s.dept, f.key AS month, f.value AS sales
FROM ( select a.*, object_construct_keep_null(a.*) AS obj
       from monthly_sales AS a) s
,TABLE(FLATTEN(input => s.obj)) f
WHERE f.KEY IN ('JAN', 'FEB', 'MAR', 'APRIL'); -- here goes list of columns
-- WHERE ARRAY_CONTAINS(f.KEY::VARIANT, ['JAN', 'FEB', 'MAR', 'APRIL'])

Related: Is there a melt command in Snowflake?

score 1 · Answer 3 · answered Apr 11 '23 at 15:00

Alternative approach using Python stored procedure and Snowpark snowflake.snowpark.DataFrame.unpivot:

CREATE OR REPLACE PROCEDURE unpivot(tablename TEXT, cols ARRAY)
RETURNS TABLE()
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
PACKAGES =('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark

def main(session: snowpark.Session, tablename: str, cols: list): 
    return session.table(tablename).unpivot("key", "value", cols)
$$;

For input data:

create or replace table monthly_sales(empid int, dept text,
                                      jan int, feb int, mar int, april int);

insert into monthly_sales(empid, dept, jan, feb, mar, april) values
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);

Call:

CALL unpivot('PUBLIC.MONTHLY_SALES', ['JAN', 'FEB', 'MAR', 'APRIL']); 
/*
EMPID   DEPT    VALUE   KEY
1   electronics JAN 100
1   electronics FEB 200
1   electronics MAR 300
1   electronics APRIL   100
2   clothes JAN 100
2   clothes FEB 300
2   clothes MAR 150
2   clothes APRIL   200
3   cars    JAN 200
3   cars    FEB 400
3   cars    MAR 100
3   cars    APRIL   50
*/

score 0 · Answer 4 · edited Mar 03 '21 at 07:06

0

I will be posting a blog shortly on solving the same problem with PIVOT, but will work just as well for UNPIVOT. I prefer to generate VIEWs for this problem (but depends on your needs). I have a simple table that has VIEW_NAME and PIVOT_COLUMN_NAME. I have a UDF that takes the VIEW_NAME and generates a VIEW with the desired SQL query.

edited Mar 03 '21 at 07:06

cigien

57,834
11
73
112

answered Mar 02 '21 at 20:15

Jeffrey Jacobs

302
1
4

1

Please don't invite people to visit your personal blog. You can add a link to that on your profile page if you want. – cigien Mar 03 '21 at 07:07
The blog on Pivot is applicable to Unpivot. You can find my blog address in my profile. – Jeffrey Jacobs Mar 03 '21 at 23:51

Snowflake how can we run an unpivot query over an array of fields instead of explicitly declare each field?

4 Answers4

Linked