Well I have this records:
Employee_Number Employee_role Group_Name
----------------------------------------------------
EMP101 C# Developer Group_1
EMP102 ASP Developer Group_1
EMP103 SQL Developer Group_2
EMP104 PLSQL Developer Group_2
EMP101 Java Developer
EMP102 Web Developer
EMP101 DBA
EMP105 DBA
EMP106 SQL Developer Group_3
EMP107 Oracle Developer Group_3
EMP101 Oracle Developer Group_3
Want to show the pivot table for above records in the following format:
Employee_Number TotalRoles TotalGroups Available Others Group_1 Group_2 Group_3
-----------------------------------------------------------------------------------------------------------------
EMP101 4 3 2 2 1 1
EMP102 2 3 1 1 1
EMP103 1 3 1 0 1
EMP104 1 3 1 0 1
EMP105 1 3 0 1
EMP106 1 3 1 0 1
EMP107 1 3 1 0 1
For the above result I am using the following script:
SELECT * FROM crosstab(
$$SELECT grp.*, e.group_name
, CASE WHEN e.employee_number IS NULL THEN 0 ELSE 1 END AS val
FROM (
SELECT employee_number
, count(employee_role)::int AS total_roles
, (SELECT count(DISTINCT group_name)::int
FROM employee
WHERE group_name <> '') AS total_groups
, count(group_name <> '' OR NULL)::INT AS available
, count(group_name = '' OR NULL)::int AS others
FROM employee
GROUP BY employee_number
) grp
LEFT JOIN employee e ON e.employee_number = grp.employee_number
AND e.group_name <> ''
ORDER BY grp.employee_number, e.group_name$$
,$$VALUES ('Group_1'),('Group_2'),('Group_3')$$
) AS ct (employee_number text
, total_roles int
, total_groups int
, available int
, others int
, "Group_1" int
, "Group_2" int
, "Group_3" int);
But:Now I want to show the pivot table for the above records by filtering the Group_Name
.
That means if I want to show the pivot table for the only Group_Name= Group_3
then it has
to show only the employee who is only belongs to the Group_Name= Group_3
not other than that.
If I want to see the employee who is belongs to the Group_3
only than it has to show me:
Employee_Number total_roles total_groups available others Group_3
-------------------------------------------------------------------------------
EMP106 1 3 1 0 1
EMP107 1 3 1 0 1
Note: As you can see in the first table the employee EMP106
and EMP107
is only belongs
to the Group_Name = Group_3
. The employee EMP101
is also belong but he also belongs to other groups
so should not appear in this table.
How to exclude the offending rows:
The
crosstab()
version adapted:But as you can see, we don't need
crosstab()
here at all. Simplify to:The column
"Group_3"
is really just noise here, because it is always1
by definition.If only a small percentage of rows is selected this way, this version with a
LATERAL
join should be substantially faster:Details for the
LATERAL
solution and performance:Simple, generic solution for any set of groups
Not optimized for performance, but easy to adapt: