PostgreSQL 交叉表查询

for each 数据库安装additional module tablefunc once，该数据库提供功能crosstab().从Postgres 9.1开始，你可以使用CREATE EXTENSION:

CREATE EXTENSION IF NOT EXISTS tablefunc;

改进的测试用例

CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

简单形式-不适合缺少的属性

crosstab(text)和101输入参数:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

返回:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |           -- !!

• 不需要铸造和重命名.
• 请注意，C的结果为incorrect:第一列填写值7.有时，这种行为是可取的，但不适用于本用例.
• 在提供的输入查询中，简单表单也被限制为exactly个三列:row_name、category、value.在下面的2参数备选方案中，没有extra columns的空间.

安全形式

crosstab(text, text)和101个输入参数:

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

返回:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7  -- !!

• 记下C的正确结果.

• second parameter可以是任何查询，每个属性返回一个row，该属性与列定义末尾的顺序匹配.通常，您会希望从基础表中查询不同的属性，如下所示:

    'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
  

手册上有.

如果要在列表中预先定义crosstabN()个变量(通常情况下，要在列表中为所有变量提供crosstabN()个变量)，则要在列表中预先定义crosstabN()个变量:

    $$VALUES ('Active'::text), ('Inactive')$$)

或(不在手册中):

    $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists

• 我用dollar quoting来简化报价.

• 您甚至可以输出different data types和crosstab(text, text)的列，只要值列的文本表示是目标类型的有效输入.这样，您可能会有不同类型的属性，并为各自的属性输出text、date、numeric等.在chapter crosstab(text, text) in the manual的末尾有一个代码示例.

db<>fiddle 100

输入行过多的影响

多余的输入行以不同的方式处理——相同("行名称"、"类别")组合的重复行——在上述示例中为(section, status).

The 1-parameter form fills in available value columns from left to right. Excess values are discarded.
Earlier input rows win.

The 2-parameter form assigns each input value to its dedicated column, overwriting any previous assignment.
Later input rows win.

Typically, you don't have duplicates to begin with. But if you do, carefully adjust the sort order to your requirements - and document what's happening.
Or get fast arbitrary results if you don't care. Just be aware of the effect.

高级范例

• Pivot on Multiple Columns using Tablefunc-还展示了提到的"额外专栏"

• 一百

\crosstabview in psql

Postgres 9.6将此元命令添加到其默认交互终端psql中.您可以运行将用作第一个crosstab()参数的查询，并将其提供给\crosstabview(立即或在下一步中).比如:

db=> SELECT section, status, ct FROM tbl \crosstabview

与上面的结果相似，但它是representation feature on the client side.输入行的处理略有不同，因此不需要ORDER BY.\crosstabview in the manual.的详细信息该页底部有更多的代码示例.

关于dba的相关回答.Daniel Vérité(psql功能的作者)的SE:

• How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?