PostgreSQL 9.3: Pivot table query

Question

I want to show the pivot table(crosstab) for the given below table.

Table: Employee

CREATE TABLE Employee
(
Employee_Number varchar(10),
Employee_Role varchar(50),
Group_Name varchar(10)
);

Insertion:

INSERT INTO Employee VALUES('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','');

Now I want to show the pivot table for the above data as shown below:

Expected Result:

Employee_Number     TotalRoles      TotalGroups       Available     Others     Group_1     Group_2
---------------------------------------------------------------------------------------------------
   EMP101               2                2                1           1           1           0
   EMP102               2                2                1           1           1           0
   EMP103               1                2                1           0           0           1 
   EMP104               1                2                1           0           0           1

Explanation: I want to show the Employee_Number, the TotalRoles which each employee has, the TotalGroups which are present to all employees, the Available shows the employee available in how many groups, the Others have to show the employee is available in other's also for which the group_name have not assigned and finally the Group_Names must be shown in the pivot format.

Answer 1

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 1
           ) 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'::text), ('Group_2')$$
   ) AS ct (employee_number text
          , total_roles  int
          , total_groups int
          , available    int
          , others       int
          , "Group_1"    int
          , "Group_2"    int);

SQL Fiddle demonstrating the base query, but not the crosstab step, which is not installed on sqlfiddle.com

Basics for crosstab:

• PostgreSQL Crosstab Query

Special in this crosstab: all the "extra" columns. Those columns are placed in the middle, after the "row name" but before "category" and "value":

• Pivot on Multiple Columns using Tablefunc

Once again, if you have a dynamic set of groups, you need to build this statement dynamically and execute it in a second call:

• Selecting multiple max() values using a single SQL statement