The Pivot function in Snowflake is a powerful tool used to rotate a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. Within the function, the user must define each value in the pivot column and put them in a comma-delimited list. Because of this, dynamically pivoting tables becomes a difficult task that cannot be done in Snowflake without using an outer python script, creating a complex function, or using Rivery. This post will describe how to dynamically pivot tables using logic rivers in Rivery.
Example: We have a table that aggregates the number sales per salesperson per month. Below you can see the structure of the table as it currently sits in Snowflake.
In this example, the user wants to pivot this table to have the values of the Salesperson column as the new columns. Using the “Non-Dynamic Pivot” river below will work, but it has a static list of names in the SQL query. What happens when a new salesperson is hired, and the static list has to change?
In order to make this example dynamic, there needs to be one river variable and 2 SQL/Script rivers created in the logic orchestration. The first river (“Retrieve New Column Values”) will retrieve the new column values and put them into a comma-delimited list which is then stored in the river variable. The second river (“Dynamic Pivot”) will create a view using the pivot function by inserting the river variable where the static list of column values lived in the “Non-Dynamic Pivot” river. Because it creates a view inside of the SQL logic, the user will have to turn on the “SQL Script” toggle.
River and Logic 1:
River and Logic 2:
Pivoted Output:
