CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
snowflake_user
snowflake_user

Reputation: 118

Unpivoting columns taken from another table

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

Upvotes: 2

Views: 378

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11046

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

Upvotes: 1

Simon D
Simon D

Reputation: 6269

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.

Upvotes: 1

Related Questions