Pivot Tables

We're all familiar with the power of Pivot tables, but building them in SQL tends to be tricky.

Snowflake has some bespoke features that make this a little easier though. In this context:

In the example below I've looked at the total hours I've watched some of my favorite programs on Netflix by year, then I've pivoted the data to show each year as a column:

We'll take our above summary table and create a new column for each year:

Displaying the data in this way helps emphasise changes across the years for each show.

Step-by-Step

Option 1: CASE

In most SQL dialects, to construct a pivot table, you'd be forced to use a string of CASE statements:

Option 2: PIVOT

In Snowflake there is a bespoke function, PIVOT, which simplifies this behavior.

The downside of both of these approaches is that you have to specify the resulting pivoted columns before you create the table, meaning you can't have a dynamic pivot table.

There is a post here that details a way to make dynamic pivot tables with stored procedures.

Naturally, you may want to turn columns into rows, which involves unpivoting. Snowflake, handily, has an UNPIVOT function that will do just that.

To unpivot our pivot table of YEAR | Friends | GLOW | The Good Place into a table of: YEAR | TITLE | HOURS WATCHED, we can do:

How We Built This

This page was built using Count. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.

This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.