Pivot a table in MySQL – Tutorial
Databases are great, especially when they are normalized. Every developer strives to come up with a database capable of storing their valuable data in a correct manner, while retaining speed both when writing and reading. Most of the time we (try to) end up in a 3NF database which addresses most of these issues. However occasionally, we come across this problem where you would prefer to have some piece of information in a column, but unfortunately it is in one or more rows. It’s at times like these that one must know how to pivot a table in MySQL
Let’s assume that we have a database that consists of three tables.
The User_Items table is the product of a M-N relationship between Users and Items. In a real world scenario each row of this table would contain the primary key of a user coupled with the primary key of an item. However in order to make this example easier to follow I am going to use names and not numbers. For each row we also store a number called Item_Amount which represents, how much money that user has spent on that kind of item
Let’s say that someone asks to generate a report, showing how much money has each user spent on each item type.
The table however that currently stores this information, needs some manipulation in order to display that report in a meaningful and easy to digest way. There are two different courses which we can follow to make this happen.
- We use a server side language like PHP to format the data in the desired way
- We pivot the table in MySQL and get the desired result right of the bat.
In order to pivot a table in MySQL we must follow 4 steps.
- Select the columns of interest
- Extend the base table with the columns that you picked
- Group and aggregate the derived table
Step 1: Select the columns of interest.
In our case, for the x-axis of the derived table we need to select the Item_Type column.
For the y-axis we obviously select the Item_Amount column
Step 2: Extend the base table with the columns that you picked
create view User_Items_Extended as ( select User_Items.Cust_Names, case when Item_Type = "Computer" then Item_Amount end as Computer, case when Item_Type = "Monitor" then Item_Amount end as Monitor, case when Item_Type = "Software" then Item_Amount end as Software from User_Items );
By doing this we add to the table the extra columns that we are interested in
Step 3: Group and aggregate the derived table
create view User_Items_Extended_Pivot as ( select Cust_Names, sum(Computer) as Computer, sum(Monitor) as Monitor, sum(Software) as Software from User_Items_Extended group by Cust_Names );
By grouping we have one row for each user. Now we just need to get rid of the NULLs
Step 4: Prettify
create view User_Items_Extended_Pivot_Pretty as ( select Cust_Names, coalesce(Computer, 0) as Computer, coalesce(Monitor, 0) as Monitor, coalesce(Software, 0) as Software from User_Items_Extended_Pivot );
That’s it! We are done
This is how you pivot a table in MySQL
On a closing note it is worth mentioning that this approach, is meant for scenarios where the columns of interest are predefined and static. If we want to pivot a table whose values are more dynamic and can be changed at runtime, a different approach using prepared statements is more suitable.