Arrange Rows to Columns Pivot Table in MySQL

In this post, I am going to demonstrate arrangement of the row data to columns which is called a MySQL Pivot table.

What is a Pivot Table?

Pivot table is one kind of summary and representation of data, like Microsoft Spreadsheets. Pivot table arranges some of row categories into column and also create count and average of that data for better representation.

Like Microsoft SQL Server, MySQL doesn’t provide a feature like Pivot Table but you can apply Pivot from the imported MySQL Table, View or the entire Excel Data Model.
For this reference you can visit this MySQL official link.

Here I am going to demonstrate Pivot example for your data of the table.

First creating sample data.

Examine this data where two employees with working hour by a different department.
In this data, employee works more than one time in some of the department.
Now requirement is, to populate two rows with four columns.
Let see,


MySQL Pivot Table

You can see the result of two employees with total working hour by each department.

The first step is inner query, in which we have selected employee working hour base on department category.
The Second step is to select this data in the outer query and apply group by clause for SUM ().

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

  • Delete all duplicate rows in MySQLSeptember 19, 2015 Delete all duplicate rows in MySQL In this post, I demonstrate for removing all rows except for one in MySQL. Before a few days ago, I was working in one of our production […]
  • Calculation of Rank in MySQL QueryAugust 29, 2015 Calculation of Rank in MySQL Query In this post, I demonstrate the calculation of Rank in MySQL query.The RANK() available in Microsoft SQL Server and MySQL doesn't have any […]
  • How to store and retrieve XML data into MySQLAugust 13, 2015 How to store and retrieve XML data into MySQL Guys very first thing, MySQL doesn't support XML data type like Microsoft SQL Server.Many times it is require to store multiple node of […]