Let’s assume that we have a database that consists of three tables.
- Users
- Items
- User_Items
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.
- Select the columns of interest
- Extend the base table with the columns that you picked
- Group and aggregate the derived table
- Prettify
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
Post a Comment
Silahkan anda tulis komentar di bawah ini !