postgresql - 转置表保留 ID

Question

很难转置下面列出的表格:

ID    Type  Col1  Col2  Col3
----------------------------
1     a      5     2     3
1     b      2     1     3
2     a      4     4     3
2     c      7     6     4

结果应该是这样的:

ID    Col    a     b     c
----------------------------
1     Col1   5     2    null
1     Col2   2     1    null 
1     Col3   3     3    null
2     Col1   4    null   7
2     Col2   4    null   6
2     Col3   3    null   4

这里有很多类似的问题，但在我看来它们与我需要的略有不同，因为我希望在结果集中保留 ID。尝试使用 tablefunc 扩展但没有成功。任何想法如何做到这一点？

Answer 1

db<>fiddle

SELECT 
    id, 
    key as col,
    max(value::int) FILTER (WHERE type = 'a') as a,
    max(value::int) FILTER (WHERE type = 'b') as b,
    max(value::int) FILTER (WHERE type = 'c') as c
FROM 
    table, 
    jsonb_each_text(
        jsonb_build_object('col1', col1, 'col2', col2, 'col3', col3)
    )
GROUP BY id, key
ORDER BY id, key

---

主要思想是我需要将三列(以及它们的值作为键/值对)与 id 列进行交叉连接。键/值对让我想起了 JSON 的想法。使用 JSON 功能，您可以创建一个 JSON 对象 (jsonb_build_object()):

id  type   col1   col2   col3   jsonb_build_object
1   a      5      2      3      {"col1": 5, "col2": 2, "col3": 3}
1   b      2      1      3      {"col1": 2, "col2": 1, "col3": 3}
2   a      4      4      3      {"col1": 4, "col2": 4, "col3": 3}
2   c      7      6      4      {"col1": 7, "col2": 6, "col3": 4}

使用 jsonb_each_text，您可以将 JSON 对象扩展为每个元素一行，并为键和值(作为文本列)增加一列:

id   type  (...)  key    value
1    a            col1   5
1    a            col2   2
1    a            col3   3
1    b            col1   2
1    b            col2   1
1    b            col3   3
2    a            col1   4
2    a            col2   4
2    a            col3   3
2    c            col1   7
2    c            col2   6
2    c            col3   4

剩下的只是分组和过滤(做一些简单的旋转)。