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:
CREATE TABLE tbl_EmployeePivotTest
INSERT INTO tbl_EmployeePivotTest VALUES
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):
,SUM(Computer_IT) AS Total_IT
,SUM(Account) AS Total_Account
,SUM(Marketing) AS Total_Marketing
,SUM(Finance) AS Total_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;
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 ().
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 dbrnd.com, 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.