Pivoting Data
1. Pivoting Data
Together, we're going to take a stab at one of Snowflake's most powerful features; pivoting data!2. Aggregated table
When aggregating data, the results typically look like this. Here, we're retrieving the course name and exam type from a table, and finding the average exam score. Sometimes, this result set can get quite bloated and difficult to quickly glean information from.3. "Pivoted" table
To help with this, we can "pivot" data. This is something that Microsoft Excel users might be familiar with. Here's the concept; we can take the values of a certain column, and turn them into columns themselves. Here, we've turned the values from the `exam_type` column into columns, and returned the average exam score for each course-exam pair.4. Creating a pivoted table
We can do this using Snowflake's `PIVOT` function. This provides us a different way to aggregate data by "pivoting" values into columns. We'll start our query with `SELECT` star, `FROM` a table. For the `PIVOT` function to work, we need to pass "star" after `SELECT`. If we'd like to remove columns from the output, we can use the `EXCLUDE` keyword. We'll see that a bit later. After `FROM` is where `PIVOT` comes in. `PIVOT` first takes an aggregation function like `SUM` or `AVG`. The field to be aggregated is passed to this function. Then, we'll use the syntax for-field-2-in (any-order-by-field-2). This turns all row values in field-2 into columns, and orders the new columns. If we'd like to only pivot a subset of a field's values into columns, we could replace `ANY` with a set of values. For now, we'll stick with `ANY`. With `PIVOT`, there's no need to use `GROUP BY`; `PIVOT` will take care of the aggregation for us!5. CTE's and pivoted data
Since we always need to "SELECT-star" when pivoting data, it's common to define a CTE before using `PIVOT`. This allows us to first prepare records before pivoting. Once the CTE is defined, the syntax is the same as before. We'll "SELECT-star" from the CTE turning the values in `exam_type` into columns.6. Comparing exam grades by type
To build the output we saw earlier, we'll first define a CTE called `exam_grades`. `exam_grades` returns four columns from the `grades` table where the `course_level` is 101. Next, we'll pivot our data. We're "SELECT star-ing" columns from the `exam_grades` CTE. BUT, we're actually using the `EXCLUDE` keyword to drop the `course_abbreviation` from the output. Within the call to `PIVOT`, we're finding the average `exam_score` and turning each value in the `exam_type` field into a column.7. Comparing exam grades by type
We've made it back to our desired output. Using `PIVOT`, we were able to make this output easier to read and digest.8. Let's practice!
Ready to put it all together? Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.