I have more then 500 columns that i need to use in unpivoting.
select col1 , col2 , col3
from ( select * from table )
unpivot (col3 for col2 in ('value', 'value2',....'value788'))
So instead of writing all these columns i used inner select of the table that holds all those values.
select col1 , col2 , col3
from ( select * from table )
unpivot (col3 for col2 in (select value_col from val_table))
but it is throwing error. unexpected select
Any suggestion
You can't use a subquery in the <column_list> of the unpivot function. From the documentation:
The column_list should contain only literal column names, not a subquery.
You would need to write a stored procedure to do this. The stored proc could take an SQL statement in as a parameter, execute it, and turn the results into a new unpivot SQL statement using javascript.
This has come up a few times so I thought about a JavaScript stored procedure. However, the labor intensive part is building out and changing the values in the in clause. For that, a simple SQL generator works well. Here's an example using the nation table in Snowflake sample data:
use schema "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1";
select '(' || listagg('\'' || N_NAME || '\'', ',') || ')'
from NATION