Pivoting

1. Pivoting

When producing reports, you often want to reshape a table to create visualizations of its data. Sometimes, that requires a rotation of a row into a column. How is that done in SQL? Pivoting is how you do it.

2. What is pivoting?

To pivot a table is to rotate that table around a pivot column. In other words, it's transposing a row into a column. Pivoting converts a long table with many rows and few columns, into a wide table, with fewer rows and more columns.

3. Benefits

Why pivot? Pivoting allows you to change a table's shape at will while preserving its data. Also, unstacked data read in a wide table is often easier to read than stacked data read in a long table. So far, this all sounds a bit abstract, so let's see pivoting in action.

4. Before and after

The left table is the count of orders for the meals with IDs 0 and 1 in Delivr's first two months of operation. The right table is the left table pivoted by the delivr_month column. Notice that the distinct values in delivr_month, June and July 2018, replaced delivr_month as columns. The table is automatically reshaped to preserve the relations of the values (in this case, the count of orders) in each row. Let's write a query to pivot this table.

5. Before table

This query returns the left table in the previous slide. It counts the distinct orders of the meals with IDs 0 and 1 in Delivr's first two months of operation, June and July 2018. You'll need to cast the count of orders to INT for later on.

6. CROSSTAB()

How do we go from the left table to the right? Enter CROSSTAB. CROSSTAB takes a source table and pivots it by one of its columns. To use CROSSTAB, first import it by using this CREATE EXTENSION statement. tablefunc is a collection of SQL functions that aren't available by default in PostgreSQL. In this sense, CREATE EXTENSION is like import in Python. Pass the string of the source query whose output you want to pivot. Then, select everything from the output of CROSSTAB, assigning it an alias (in this case, ct). Then, in parenthesis, list the pivoted table's columns and their data types. Let's use CROSSTAB to pivot the left table to the right one.

7. Using CROSSTAB()

Start by creating the tablefunc extension to import CROSSTAB. Then, select star from CROSSTAB. Place two dollar signs after the opening parentheses and before the closing ones. This is because CROSSTAB pivots the table provided to it as a string, and two dollar signs enclosing a query is a safe way to convert it to a string. Then, write the left table's query within the pair of two dollar signs. Finally, assign ct as an alias to CROSSTAB's output, and list its columns and their data types within the parentheses. Since you're pivoting by delivr_month, place meal_id and the values of delivr_month, June and July 2018, as columns (encased by double quotation marks because they're column names, not values now). This is why you cast the count of orders to INT earlier -- it's to make sure that the types of the values in the unpivoted and pivoted values are the same.

8. Before and after revisited

This is how you use CROSSTAB to reshape a long table to a wide table. Wide tables are often easier to read, because you can easily track an entire row's values. They're also required for some visualizations in SQL.

9. Pivoting

In the following exercises, you'll use CROSSTAB to pivot tables into the shape you want.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.