sql - PostgreSQL - 从长格式到宽格式

Question

假设我有一些长格式的表格，如下所示:

CREATE TEMP TABLE tmp (
  id int,  
  value varchar,
  id2 int,
  key int);

INSERT INTO tmp VALUES 
  (1, 87.1, 1444, 102),
  (2, 144.9, 13921, 3), 
  (3, 'A032333', 13921, 7),
  (4, 88.9, 13921, 102),
  (5, 'JDS-SJDDD', 13921, 101),
  (6, 90000, 13921, 140),
  (7, 101.1, 33113, 133),
  (8, 'KKL-KKIDD', 33113, 101),
  (9, 0, 33113, 239),
  (10, 933.1, 33113, 250); 

我需要将此数据转换为宽格式，如下所示:

CREATE TEMP TABLE tmp2 (
  id2 integer,
  k3 numeric,
  k7 varchar,
  k101 varchar,
  k102 numeric,
  k133 numeric,
  k140 int,
  k239 int,
  k250 numeric);

INSERT INTO tmp2 VALUES 
  (1444, NULL, NULL, NULL, 87.1, NULL, NULL, NULL, NULL),
  (13921, 144.9, 'A032333', 'JDS-SJDDD', 88.9, NULL, 90000, NULL, NULL), 
  (33113, NULL, NULL, 'KKL-KKIDD', NULL, 101.1, NULL, 0, 933.1);

我尝试过多个CASE WHEN:

SELECT id2, 
    CASE WHEN key = 3 THEN value END AS a3,
    CASE WHEN key = 7 THEN value END AS a7,
    CASE WHEN key = 101 THEN value END AS a101,
    CASE WHEN key = 102 THEN value END AS a102,
    CASE WHEN key = 133 THEN value END AS a133,
    CASE WHEN key = 140 THEN value END AS a140,
    CASE WHEN key = 239 THEN value END AS a239,
    CASE WHEN key = 250 THEN value END AS a250
FROM tmp;

但是，输出为 id2 保留了多行，而每个值只保留一行就足够了。如何调整？我正在考虑类似 GROUP BY + COALESCE 的方法，但是 COALESCE 跨行查找值，我需要返回第一个非空值关于专栏。更重要的是，这种方法似乎非常麻烦，因为我的原始数据将包含大约 2000 个结果列，因此使用 CASE WHEN 指定每一列将产生大量代码。有捷径吗？如果没有，如何实现？

Answer 1

Postgres支持使用filter关键字进行条件聚合，所以我推荐:

SELECT id2, 
       MAX(value) FILTER (WHERE key = 3) AS a3,
       MAX(value) FILTER (WHERE key = 7) AS a7,
       MAX(value) FILTER (WHERE key = 101) AS a101,
       MAX(value) FILTER (WHERE key = 102) AS a102,
       MAX(value) FILTER (WHERE key = 133) AS a133,
       MAX(value) FILTER (WHERE key = 140) AS a140,
       MAX(value) FILTER (WHERE key = 239) AS a239,
       MAX(value) FILTER (WHERE key = 250) AS a250
FROM tmp
GROUP BY id2;

但关键思想是GROUP BY。