Creating pivot tables

1. Creating pivot tables

If you have experience using spreadsheet programs, you might have come across the concept of pivoting data. If not, this is simply a way to summarize data in a more compact and interpretable orientation.

2. Multiple category records

Returning to New York City restaurant inspection results, notice that restaurants receive grades at different stages of their existence.

3. Accessing inspection grades by type

Imagine that we are interested in getting a better understanding of how restaurants maintain their adherence to health standards throughout the city. To get a better understanding of city-wide data, we could look at the grades that restaurants receive for different types of inspections. The SELECT query displayed here will generate these results.

4. Aggregated inspection results by type

The previous query produces the results shown here. The inspection types are paired with a letter grade. The count column displays the number of records containing each pair.

5. Changing (pivoting) data orientation

There are only 7 inspection types but the previous results required displaying 18 rows. It would be helpful to see all grade counts for a given inspection type in a single row. The pivot table displayed here provides such a compact view of the data. Let's go over how to produce these results.

6. The FILTER clause

One way that we can pivot data is by using PostgreSQL's FILTER clause. The FILTER clause applies an aggregation function over a subset of records. An accompanying WHERE clause determines the subset of records provided to the aggregation function. FILTER clauses define the columns in the SELECT list of queries.

7. The FILTER clause

In this example, we can imagine having access to a product table with a quantity sold column. A FILTER clause can calculate the average order size for products with more than 1 sale. In general, an aggregation function applied to an expression (the column values in our example) precedes the FILTER clause. A WHERE clause following the FILTER keyword specifies a condition the records must satisfy.

8. The pivot table query

FILTER clauses can help us to re-orient our data to create a pivot table. Here a summary column is defined in the SELECT list. Subsequent list elements include a FILTER clause to provide the values for each pivot column.

9. The pivot table output

The output of a SELECT statement constructed in this way is a table. Each value in the summary column occupies a row in the table. The aggregation values populate the pivot column for each defined pivot column.

10. Pivoting restaurant inspection data

Let's look at the SELECT statement used to build the pivot table displayed earlier in this lesson. The COUNT() function computes the number of grades with a certain letter for each inspection type. We use a FILTER clause for each possible letter grade. The WHERE clause specifies the letter grade used to define a subset of records. In this example, the grade (enclosed in double-quotes) is the label for the resulting column.

11. Pivot table output for inspection data

The previous query produces the desired output by re-orienting (or pivoting) the data. Each inspection type occupies a row. The values in the row represent the count of each grade value. We can see from these results that the restaurants in our sample data all received As for the initial inspection. However, inspections beyond the initial inspection have a wider range of grades. It seems that restaurants have trouble maintaining cleanliness standards that existed when first opening.

12. Let's practice!

Now that you've learned how to pivot data in PostgreSQL, you'll have the opportunity to practice this technique in the following exercises.

Create Your Free Account

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