Pivot Tables
Published 2025 January 21
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 1 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 SQL2 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 Sheets3 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 #
-
Wikipedia contributors. (2024, October 13). Pivot table. In Wikipedia, The Free Encyclopedia. Retrieved January 23, 2025, from https://en.wikipedia.org/w/index.php?title=Pivot_table&oldid=1251019122 ↩︎
-
Zhiyanov, A. Building a pivot table in sqlite. Anton Zhiyanov. Retrieved January 23, 2025, from https://antonz.org/sqlite-pivot-table/ ↩︎
-
Create & use pivot tables. Google Docs Editors Help. Retrieved January 23, 2025, from https://support.google.com/docs/answer/1272900 ↩︎