Create a dynamic pivot function with multiple aggregate functions in Snowflake
This video follows on from • Create a Dynamic Pivot Stored Procedu...
The sql used in this video is below. Just one thing to note - YouTube does not allow less-than and greater-than signs in descriptions, so ive replaced that part with LESSTHANGREATERTHAN. That will need to be put back before you use the script.
execute immediate
$$
declare
column_list_in string default (select listagg(distinct category, ',') from "EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE" WHERE CONTAINS("CATEGORY", 'A'));
table_in string default '"EXAMPLE_DB"."EXAMPLE_SCHEMA"."EXAMPLE_TABLE"';
pivot_column_name string default 'CATEGORY';
value_column_name string default 'VALUE';
pivot_function1 string default 'SUM';
pivot_function2 string default 'COUNT';
begin
--make a comma-seperated string of the columns to pivot
let column_selection string := '(\'' || regexp_replace(column_list_in, ',', '\',\'') || '\')';
--create a query to get the names of the unpivoted columns
let unpivoted_columns_sql string := 'select listagg(column_name, \',\') within group (order by ordinal_position)
from ' || split_part(table_in, '.', 1) || '.information_schema.columns
where table_catalog = ' || replace(split_part(table_in, '.', 1), '"', '\'') || '
and table_schema = ' || replace(split_part(table_in, '.', 2), '"', '\'') || '
and table_name = ' || replace(split_part(table_in, '.', 3), '"', '\'') || '
and column_name not in (\'' || value_column_name || '\',\'' || pivot_column_name || '\')';
--execute the query and bring the result into a string called column_labels
let column_labels string;
let unpivoted_columns resultset := (execute immediate : unpivoted_columns_sql);
let c cursor for unpivoted_columns;
open c;
fetch c into column_labels;
close c;
--add the name of the aggregate function to the pivoted column names
let column_labels_agg1 string := replace(column_list_in, ',' ,'_' || pivot_function1 || ',') || '_' || pivot_function1;
let column_labels_agg2 string := replace(column_list_in, ',' ,'_' || pivot_function2 || ',') || '_' || pivot_function2;
--create strings for aliases
let counter integer := 1;
let column_join string := '';
let column_w_alias string := '';
while (split_part(column_labels, ',', counter) LESSTHANGREATERTHAN '') do
--get a list of unpivoted columns with an alias, seperated by equals signs
column_join := column_join || 'agg1.' || split_part(column_labels, ',', counter) || ' = agg2.' || split_part(column_labels, ',', counter) || ' and ';
--get a list of un-pivoted column names to enter into the final select statement
column_w_alias := column_w_alias || 'agg1.' || split_part(column_labels, ',', counter) || ',';
counter := counter + 1;
end while;
--remove the trailing parts of the strings string
column_join := left(column_join, length(column_join) - 4);
column_w_alias := left(column_w_alias, length(column_w_alias) - 1);
--join together as a sql query
let final_sql string := 'with agg1_table as (
select *
from ' || table_in || '
pivot(' || pivot_function1 || '("' || value_column_name || '")
for ' || pivot_column_name || ' in ' || column_selection || ')
as p(' || column_labels || ',' || column_labels_agg1 || ')
),
agg2_table as (
select *
from ' || table_in || '
pivot(' || pivot_function2 || '("' || value_column_name || '")
for ' || pivot_column_name || ' in ' || column_selection || ')
as p(' || column_labels || ',' || column_labels_agg2 || ')
)
select ' || column_w_alias || ',' || column_labels_agg1 || ',' || column_labels_agg2 ||'
from agg1_table agg1
inner join agg2_table agg2
on ' || column_join;
--excecute the query
let output resultset := (execute immediate : final_sql);
--return the output of the query
return table(output);
$$;
Watch video Create a dynamic pivot function with multiple aggregate functions in Snowflake online without registration, duration hours minute second in high quality. This video was added by user DotPi 26 March 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,592 once and liked it 18 people.