时间戳

首页 > 解决方案 > 我如何透视 PostgreSQL 表？

sql - 我如何透视 PostgreSQL 表？

问题描述

我有company_representatives一个看起来像这样的表：

在此处输入图像描述

创建表脚本：

CREATE TABLE IF NOT EXISTS company_representatives (
 _id          integer NOT NULL,
 name         varchar(50) NOT NULL,
 surname      varchar(100) NOT NULL,
 date_of_join date NOT NULL,
 role         varchar(250) NOT NULL,
 company_id   integer NOT NULL,
 CONSTRAINT PK_company_representatives PRIMARY KEY ( _id ),
 CONSTRAINT FK_144 FOREIGN KEY ( company_id ) REFERENCES companies ( _id )
);

INSERT INTO company_representatives VALUES
(1,'random name','random surname', '2001-01-23', 'CEO', 1),
(2,'next random name','next random surname', '2001-01-23', 'Co-founder', 1),
(3,'John','Doe', '2003-02-12', 'HR', 1),
(4,'Bread','Pitt', '2001-01-23', 'Security officer', 1),
(5,'Toast','Malone', '1997-11-05', 'CEO', 2),
...

我需要旋转此表以使其列看起来像这样：

company_id | CEO | Co-Founder | HR | Security Officer
    1         1         2       3            4          "_id of company's representatives"
    2         5         6       7            8
    3         9        10       11          12

标签： sqlpostgresqlpivotpostgresql-13

解决方案

您可以FILTER直接在SELECT子句中使用：

SELECT DISTINCT ON (company_id)
  company_id,
  count(*) FILTER (WHERE role = 'CEO') AS CEO,
  count(*) FILTER (WHERE role = 'Co-founder') AS "Co-Founder",
  count(*) FILTER (WHERE role = 'HR') AS HR,
  count(*) FILTER (WHERE role = 'Security officer') AS "Security Officer"
FROM company_representatives
GROUP BY company_id;

有问题的是，不清楚附加到角色的值实际上意味着什么，所以我假设你只是想计算它们。如果没有，只需将其更改为其他聚合函数。

编辑（见评论）：使用数据透视表crosstab，假设所有公司的每个角色都有一个记录：

SELECT *
FROM crosstab(
 'SELECT company_id,  _id, name
  FROM company_representatives ORDER BY company_id,role' 
) AS ct(company_id integer,ceo text,co_founder text,hr text,security_officer text);

演示：db<>fiddle

推荐阅读