Using Variable to make Dynamic Column Aliases in Snowflake

user_14067742 0
Viewed 868

I have a table that lists a series of values for 12 months across several columns (ie. month_1_value, month_2_value, etc.), along with other columns indicating the month associated (ex: month_1_name = 202101, month_2_name = 202102, etc.)

I am trying to set it up so that the column 'month_1_value' will be called '202101', month_2_value will be '202102' etc.

This is typically something that is extremely easy to do in a language like Python, but I am struggling to find the correct method in SQL/Snowflake.

I am able to set a variable to contain the value using set min_month = min(month_1_name), but I am unable to use that as the new alias in a view or stored proc from what I have tried.

The general idea I am looking to achieve would be something like this:

set min_month = min(month_1_name);

SELECT
month_1_value as $min_month
, month_2_value as $min_month+1
.....
FROM
Table

It seems like there should be a fairly simple way to do this but I have not been able to find it yet. Even something like setting the variable first in Javascript, and then having another variable reference that variable as a string seems like it would work but I am struggling to find a clear method. Any advice would be appreciated.

snowflake-cloud-data-platform dynamic-sql sql
1 Answers

As Greg says, you will need a stored procedure.

Check this blog post I wrote:

It's focused on doing pivots, but it has the basics of what you want.

Basically it allows you to call the stored procedure call pivot_prev_results();, which takes the results of the previous query, and creates results with dynamic column names, that the next query can use.

Inside the stored procedure a dynamic query is created, that uses the previous results:

pivot_query = `
select *
from (select * from table(result_scan(last_query_id(-2))))
pivot(max(pivot_value) for pivot_column in (${col_list}))
`
var stmt2 = snowflake.createStatement({sqlText: pivot_query});
stmt2.execute();

Your stored procedure could do a similar process, but create a view instead.

user_1324380
Related