This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on the arrangement of the row data into the 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 the row categories into column and also create count and average of that data for better image.
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.
Create a sample table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE DATABASE Employee; CREATE TABLE Employee.EmployeePivotTest ( EmpName VARCHAR(255) ,EmpDeptName VARCHAR(255) ,EmpAvgWorkingHours INTEGER ); INSERT INTO Employee.EmployeePivotTest VALUES ('Anvesh','Computer-IT',226) ,('Anvesh','Computer-IT',100) ,('Anvesh','Computer-IT',200) ,('Anvesh','Computer-IT',752) ,('Anvesh','Account',142) ,('Anvesh','Marketing',110) ,('Anvesh','Finance',236) ,('Anvesh','Account',120) ,('Neevan','Computer-IT',120) ,('Neevan','Finance',852) ,('Neevan','Account',326) ,('Neevan','Marketing',50) ,('Neevan','Finance',140); |
Examine above data where two employees with working hour for a different department.
In this sample data, employees are working more than one time in some of the department.
Now the requirement is, to populate two rows with four columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT EmpName ,SUM(Computer_IT) AS TotalHour_Computer_IT ,SUM(Account) AS TotalHour_Dept_Account ,SUM(Marketing) AS TotalHour_Dept_Marketing ,SUM(Finance) AS TotalHour_Dept_Finance FROM ( SELECT EmpName ,CASE WHEN EmpDeptName = 'Computer-IT' THEN EmpAvgWorkingHours END AS Computer_IT ,CASE WHEN EmpDeptName = 'Account' THEN EmpAvgWorkingHours END AS Account ,CASE WHEN EmpDeptName = 'Marketing' THEN EmpAvgWorkingHours END AS Marketing ,CASE WHEN EmpDeptName = 'Finance' THEN EmpAvgWorkingHours END AS Finance FROM Employee.EmployeePivotTest ) AS T GROUP BY EmpName; |
Result:
You can see the result of two employees with a total working hour for an each department.
The first phase is an inner query, in which we selected employee working hour based on department category.
The second phase is for selecting this data in the outer query and applies group by clause for SUM ().
