This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to demonstrate arrangement of the row data to columns which is called a something like Pivot table in PostgreSQL.
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, PostgreSQL doesn’t provide a feature like Pivot Table, We can achieve using SQL Query.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE tbl_EmployeePivotTest ( EmpName VARCHAR(255) ,EmpDeptName VARCHAR(255) ,EmpAvgWorkingHours INTEGER ); INSERT INTO tbl_EmployeePivotTest VALUES ('Anvesh','Computer-IT',226) ,('Anvesh','Computer-IT',100) ,('Anvesh','Account',142) ,('Anvesh','Marketing',110) ,('Anvesh','Finance',236) ,('Anvesh','Account',120) ,('Jeeny','Computer-IT',120) ,('Jeeny','Finance',852) ,('Jeeny','Account',326) ,('Jeeny','Marketing',50) ,('Jeeny','Finance',140); |
Examine this data where two employees with working hour in a different department.
In this data, employee worked more than one time in some of the department.
Now requirement is, to populate different column for different department with total of working hours.
SQL Query to PIVOT Table (Using GroupBy Clause):
|
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 Total_IT ,SUM(Account) AS Total_Account ,SUM(Marketing) AS Total_Marketing ,SUM(Finance) AS Total_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 tbl_EmployeePivotTest ) AS T GROUP BY EmpName; |
The Result:
|
1 2 3 4 |
empname | total_it | total_account | total_marketing | total_finance ---------+----------+---------------+-----------------+--------------- Anvesh | 326 | 262 | 110 | 236 Jeeny | 120 | 326 | 50 | 992 |
In the above result,
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 ().
