Create View from Pivot

user_18622831 0
Viewed 33

Is there a way to create a view in snowflake that would hold the Pivot data from the dynamic pivot technique? I am able to run the code and it works good but I would like to use it as a view the pivot column names changes, which is why I like the dynamic Pivot technique.

select name pivot_column
  , date_trunc(quarter, month) month
  , sum(month_views) pivot_value
from hero_views
group by 1,2;

call pivot_prev_results();

select *
from table(result_scan(last_query_id(-2)));


create or replace procedure pivot_prev_results()
returns string
language javascript
execute as caller as
$$
  var cols_query = `
      select '\\'' 
        || listagg(distinct pivot_column, '\\',\\'') within group (order by pivot_column)
        || '\\'' 
      from table(result_scan(last_query_id(-1)))`;
  var stmt1 = snowflake.createStatement({sqlText: cols_query});
  var results1 = stmt1.execute();
  results1.next();
  var col_list = results1.getColumnValue(1);
  
  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();
  return `select * from table(result_scan('${stmt2.getQueryId()}'));\n  select * from table(result_scan(last_query_id(-2)));`;
$$;
snowflake-cloud-data-platform
1 Answers

The problem with pivots and SQL is that usually for SQL to work you need to know the output columns at compile time.

I'm the author of that dynamic pivot code, and it works this way as the nature of the dynamic pivot is that we don't know what columns will be in the output. And that's why we can't create a view for it, because we don't know the columns to use.

The native pivot asks for a static list of columns for that reason - then the columns are known at compile time.

user_1324380
Related