PostgreSQL如何实现透视表行转列（长表转宽表）查询

基础说明 #

现有客户支付表逻辑结构如下：

customer_name   TEXT    -- 客户姓名
product_type    TEXT    -- 购买产品类型
total_paid      NUMERIC -- 支付金额

原表存储样例：

需求为按客户维度做行转列，每个客户单行输出，不同产品类型作为独立列存储对应支付金额，目标输出结构：

方案1：条件聚合（无依赖，首选） #

不需要安装任何扩展，全版本PostgreSQL通用，逻辑直观易维护，是绝大多数业务场景的首选方案。
假设原表名为customer_payment，SQL代码如下：

SELECT
  customer_name,
  SUM(CASE WHEN product_type = 'car' THEN total_paid ELSE 0 END) AS car,
  SUM(CASE WHEN product_type = 'motorbike' THEN total_paid ELSE 0 END) AS motorbike
FROM customer_payment
GROUP BY customer_name
ORDER BY customer_name;

• 如果确定单个客户对应单个产品类型仅有1条记录，可以把SUM替换为MAX/MIN，执行效果一致
• 如果需要客户未购买对应产品时返回空值而非0，去掉语句中的ELSE 0即可

方案2：crosstab函数（tablefunc扩展，适合多列/大数据量场景） #

PostgreSQL自带的tablefunc扩展提供了专用行转列函数crosstab，大数据量下性能优于条件聚合，适合产品类型枚举值较多的场景。

1. 首先启用扩展（单库仅需执行一次，无需重复运行）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行透视查询：

SELECT *
FROM crosstab(
  -- 基础数据查询，必须按「分组维度、透视列、统计值」的顺序返回字段，且提前排序
  'SELECT customer_name, product_type, total_paid
   FROM customer_payment
   ORDER BY 1, 2',
  -- 透视列枚举查询，用于固定输出列的顺序
  'SELECT DISTINCT product_type FROM customer_payment ORDER BY 1'
) AS pivot_result(
  -- 必须显式定义返回列的名称和类型，顺序和上方枚举查询返回的产品类型顺序完全一致
  customer_name TEXT,
  car NUMERIC,
  motorbike NUMERIC
);

• 注意AS后定义的返回列顺序必须和第二个子查询返回的产品类型顺序完全匹配，否则会出现数据错位
• 如果后续需要支持动态增减产品类型无需修改SQL，可以基于这个逻辑封装动态SQL，自动生成返回列定义

注意事项 #

• 两种方案都兼容同个客户同个产品类型存在多条支付记录的场景，会自动累加计算总支付金额
• 输出结果可以直接用于创建视图、写入持久化表等后续操作

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