如何在PostgreSQL中使用crosstab()实现表转置？

方法一：条件聚合（推荐，无需额外扩展） #

如果你的用户列表是固定的（User1、User2、User3），用条件聚合写法最直接，不需要依赖任何扩展：

SELECT
  "Category",
  SUM(CASE WHEN "Operator" = 'User1' THEN piece ELSE 0 END) AS "User1",
  SUM(CASE WHEN "Operator" = 'User2' THEN piece ELSE 0 END) AS "User2",
  SUM(CASE WHEN "Operator" = 'User3' THEN piece ELSE 0 END) AS "User3"
FROM test
GROUP BY "Category"
ORDER BY "Category";

这个查询按Category分组，对每个用户的piece求和，无数据的用户自动填充0，完全匹配你要的结果。

方法二：使用crosstab函数 #

如果需要动态生成列（比如用户不固定），可以用PostgreSQL的crosstab函数，但需先启用tablefunc扩展：

1. 启用扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写交叉表查询 #

crosstab默认会把缺失数据返回NULL，用COALESCE转换为0：

SELECT
  "Category",
  COALESCE("User1", 0) AS "User1",
  COALESCE("User2", 0) AS "User2",
  COALESCE("User3", 0) AS "User3"
FROM crosstab(
  -- 生成源数据：按Category和Operator分组求和并排序
  'SELECT "Category", "Operator", SUM(piece)
   FROM test
   GROUP BY "Category", "Operator"
   ORDER BY 1, 2',
  -- 指定要生成的列（用户列表）
  'SELECT unnest(array[''User1'', ''User2'', ''User3''])'
) AS ct("Category" text, "User1" int, "User2" int, "User3" int);

执行后即可得到预期结果。

内容的提问来源于stack exchange，提问作者Massimo Maioli