PostgreSQL交叉表查询

Question

PostgreSQL交叉表查询

有没有人知道如何在PostgreSQL中创建交叉表查询？

例如，我有下表：

Section    Status    Count

A          Active    1

A          Inactive  2

B          Active    4

B          Inactive  5

我想查询返回以下交叉表：

Section    Active    Inactive

A          1         2

B          4         5

这可能吗？

Answer 1

您可以使用附加模块tablefunc的crosstab()功能- 您必须为每个数据库安装一次。从PostgreSQL 9.1开始，你可以使用它：CREATE EXTENSIONCREATE EXTENSION tablefunc;在你的情况下，我相信它看起来像这样：CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);INSERT INTO t VALUES ('A', 'Active',   1);INSERT INTO t VALUES ('A', 'Inactive', 2);INSERT INTO t VALUES ('B', 'Active',   4);INSERT INTO t VALUES ('B', 'Inactive', 5);SELECT row_name AS Section,       category_1::integer AS Active,       category_2::integer AS InactiveFROM crosstab('select section::text, status, count::text from t',2)            AS ct (row_name text, category_1 text, category_2 text);

Answer 2

SELECT section,       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status  value as a separate column explicitlyFROM tGROUP BY section

Answer 3

使用JSON聚合的解决方案：CREATE TEMP TABLE t (  section   text, status    text, ct        integer  -- don't use "count" as column name.);INSERT INTO t VALUES   ('A', 'Active', 1), ('A', 'Inactive', 2), ('B', 'Active', 4), ('B', 'Inactive', 5)                   , ('C', 'Inactive', 7); SELECT section,       (obj ->> 'Active')::int AS active,       (obj ->> 'Inactive')::int AS inactiveFROM (SELECT section, json_object_agg(status,ct) AS obj      FROM t      GROUP BY section     )X