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