PostgreSQL 9.3：数据透视表查询

Question

我想显示给定下表的数据透视表（交叉表）。

桌子： Employee

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

插入：

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','');

现在，我想显示上述数据的数据透视表，如下所示：

预期结果 ：

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

说明
：我想显示Employee_Number的TotalRoles每个员工都有，TotalGroups这是目前所有员工，Available节目中有多少组的员工可用，Others必须显示该员工在其他的也GROUP_NAME没有分配给哪些可用最后Group_Names必须以数据透视格式显示。

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
演示基本查询，但不演示交叉表步骤，该步骤未安装在sqlfiddle.com上