Pivot a table in MySQL 5.x

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.

1. Users
2. Items
3. User_Items

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.

1. We use a server side language like PHP to format the data in the desired way
2. 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.

1. Select the columns of interest
2. Extend the base table with the columns that you picked
3. Group and aggregate the derived table
4. Prettify

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
http://sqlfiddle.com/#!2/90233/1/0
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.

Source: stratosprovatopoulos.com