PostgreSQL 9.4行转列实现：将分组统计结果转为宽表

嘿，你要的这个结果其实就是典型的**行转列（透视表）**需求，在PostgreSQL 9.4里可以用crosstab函数来搞定，不过得先做个小准备——启用专门的扩展，具体步骤如下：

1. 先启用tablefunc扩展 #

crosstab函数属于tablefunc扩展包，默认不会自动加载，所以先执行这条语句开启它：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写透视查询语句 #

用crosstab把你的分组结果转换成想要的格式，完整语句如下：

SELECT *
FROM crosstab(
  -- 第一个参数：源数据查询，必须返回三列：行标识(rulename)、列标识(status)、对应值(count)
  'SELECT rulename, status, count(*) 
   FROM triggered_rule_info 
   GROUP BY rulename, status 
   ORDER BY rulename, status',
  -- 第二个参数：指定要作为列的status值，顺序和你期望的结果列一致
  'SELECT unnest(''{"ack","open","close"}''::text[])'
) AS ct(rulename text, ack int, open int, close int);

语句说明： #

• 第一个子查询就是你原本的分组统计，额外加上ORDER BY是为了让crosstab能正确匹配行和列的对应关系；
• 第二个子查询用unnest把数组中的status值拆成单行，用来定义透视后的列；
• 最后的AS ct(...)必须明确指定结果集的列名和数据类型，要和第二个子查询的列顺序完全对应。

3. 可选：把NULL替换为0 #

如果希望像eorule2这种没有close状态的行，对应的列显示0而不是NULL，可以用COALESCE函数处理：

SELECT 
  rulename,
  COALESCE(ack, 0) AS ack,
  COALESCE(open, 0) AS open,
  COALESCE(close, 0) AS close
FROM crosstab(
  'SELECT rulename, status, count(*) 
   FROM triggered_rule_info 
   GROUP BY rulename, status 
   ORDER BY rulename, status',
  'SELECT unnest(''{"ack","open","close"}''::text[])'
) AS ct(rulename text, ack int, open int, close int);

这样执行后就能得到你想要的格式啦～

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