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.
CREATE DATABASE Employee;
CREATE TABLE Employee.EmployeePivotTest
INSERT INTO Employee.EmployeePivotTest VALUES
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.
,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
,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
) AS T
GROUP BY EmpName;
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 ().