Postgres中如何将行值转换为列名实现表数据透视

针对你需要将status行值转为列、对应展示timestamp值的需求，可通过以下两种方式实现，两种方式都能完全匹配你给出的预期结果结构：

方案1：条件聚合（无依赖通用写法） #

不需要安装任何扩展，直接通过条件判断聚合取值即可，逻辑透明易调试，完全复用你原有查询的逻辑：

SELECT
  id,
  MAX(CASE WHEN status = 'started' THEN timestamp END) AS started,
  MAX(CASE WHEN status = 'paused' THEN timestamp END) AS paused,
  MAX(CASE WHEN status = 'killed' THEN timestamp END) AS killed
FROM (
  -- 此处直接嵌套你原来能正确返回结果的查询即可
  select distinct on (status) id, status, timestamp
  from my_table
  where id = 1
  order by status, timestamp
  limit 500
) t
GROUP BY id;

注意：由于你原查询已经通过DISTINCT ON保证每个status仅返回1条记录，这里聚合函数用MAX/MIN效果完全一致，仅用于提取分组内对应状态的非空时间值，不会改变原查询的结果准确性。

方案2：crosstab交叉表函数（适合多透视列场景） #

如果后续需要增加更多status透视列，可使用PostgreSQL自带tablefunc扩展提供的crosstab函数，写法更简洁、执行效率更高：

1. 首次使用前先启用扩展（库级别仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 透视查询写法：

SELECT *
FROM crosstab(
  -- 第一个参数为原始数据查询，必须按「分组列、透视类别列、值列」的顺序返回结果并排序
  'select distinct on (status) id, status, timestamp
   from my_table
   where id = 1
   order by status, timestamp
   limit 500',
  -- 第二个参数明确指定透视列的取值和顺序，必须和下方结果列的定义顺序完全一致
  'VALUES (''started''), (''paused''), (''killed'')'
) AS ct (
  id INTEGER,
  started TIMESTAMP,
  paused TIMESTAMP,
  killed TIMESTAMP
);

方案选型参考 #

• 如果你没有数据库扩展创建权限、或者仅需要固定3个状态的透视结果，优先选条件聚合写法，无依赖、不易出错
• 如果你需要透视的状态值较多、且有扩展创建权限，选crosstab写法，后续维护新增列更方便

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