Pivot Tables

During my undergrad, I took a course on engineering computation. It ended up being a crash course in Excel and VBA. It opened my eyes to the myriad uses of Excel. One feature that I didn't quite grasp was pivot tables. They were cool but the week we covered them in was not enough to understand them completely.

Fast forward a couple years and I am tasked with creating some reports. The reports all use the same data but need to be viewed in different ways. This problem tickled my brain until I eventually wound my way back to pivot tables. So what exactly is a pivot table? We learn from Wikipedia ¹ that:

A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories. The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.

A pivot table allows us to ask questions of our data that we would not be able to with a 2 dimensional table. We can select one or more categories and "pivot" the rows by category then we summarize the values of each row by that category. Its a little tough to explain so let's take a look at an example.

Example §

Here we have some sample data of company sales. The following query does not give us a very compelling view of the data.

select      id,
            company,
            item,
            amount,
            created_at
from        company_sales;

# +----+-----------------+----------+--------+------------+
# | id |     company     |   item   | amount | created_at |
# +----+-----------------+----------+--------+------------+
# | 1  | Apple           | TV       | 1200   | 2015-02-15 |
# | 2  | Samsung         | Phone    | 800    | 2015-05-20 |
# | 3  | Hewlett-Packard | Computer | 950    | 2016-07-30 |
# |                       ...                             |
# | 48 | Hewlett-Packard | TV       | 1070   | 2016-02-19 |
# | 49 | Apple           | TV       | 1220   | 2018-06-22 |
# | 50 | Samsung         | Phone    | 810    | 2017-08-13 |
# +----+-----------------+----------+--------+------------+

Pivot Example #1

This is where pivot tables shine. We have a bunch of raw data but we aren't quite sure what to do with it. We can begin by asking a question. For example, "What is the total sales of each item for each company?". Now we have a more interesting result!

select      company,
            sum(case when item = 'Computer' then amount else 0 end) as computer_sales,
            sum(case when item = 'TV' then amount else 0 end) as tv_sales,
            sum(case when item = 'Phone' then amount else 0 end) as phone_sales
from        company_sales
group by    company

# +-----------------+----------------+----------+-------------+
# |     company     | computer_sales | tv_sales | phone_sales |
# +-----------------+----------------+----------+-------------+
# | Apple           | 3390           | 8390     | 4140        |
# | Hewlett-Packard | 5970           | 5440     | 3750        |
# | Samsung         | 5510           | 5445     | 4840        |
# +-----------------+----------------+----------+-------------+

Pivot Example #2

Let's ask another question! What were the total yearly sales of each item?

select      item,
            sum(case when strftime('%Y', created_at) = '2015' then amount else 0 end) as '2015',
            sum(case when strftime('%Y', created_at) = '2016' then amount else 0 end) as '2016',
            sum(case when strftime('%Y', created_at) = '2017' then amount else 0 end) as '2017',
            sum(case when strftime('%Y', created_at) = '2018' then amount else 0 end) as '2018'
from        company_sales
group by    item

# +----------+------+------+------+------+
# |   item   | 2015 | 2016 | 2017 | 2018 |
# +----------+------+------+------+------+
# | Computer | 4500 | 5390 | 1820 | 3160 |
# | Phone    | 2270 | 1590 | 5270 | 3600 |
# | TV       | 5780 | 5385 | 4600 | 3510 |
# +----------+------+------+------+------+

SQL Limitations §

Implementation of pivot table functions in SQL is vendor specific. However, you can create a pivot table using plain old SQL² as I have shown above. Writing plain SQL could get a little tiring and it doesn't have the nicest output. I am not an expert in business intelligence tools, but, I did find Google Sheets³ to be exceptionally simple to use for creating a pivot table. Sheets has a nice drag and drop editor that makes ad hoc data exploration a breeze. I had a lot of fun just seeing what I could come up with!

References §