PostgreSQL 9.3:数据透视表查询 [英] PostgreSQL 9.3: Pivot table query

问题描述

我想显示给定下表的数据透视表(交叉表).

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

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

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

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

预期结果:

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必须以数据透视格式显示.

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.

推荐答案

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

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

交叉表的基础:

• PostgreSQL交叉表查询

此交叉表中的特殊:所有额外"列.这些列位于中间，在行名"之后，在类别"和值"之前:

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

• 使用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:

• 使用以下方法选择多个max()值一条SQL语句

这篇关于PostgreSQL 9.3:数据透视表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！