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

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.

Enquirer: alim1990

||

Solution #1:

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
;

enter image description here

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);

enter image description here

Respondent: Felipe Hoffa

Solution #2:

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.

Respondent: Jeffrey Jacobs

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.