'PostgreSQL 9.3: Filter in Pivot table query

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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source