如何在 postgresql 中进行透视

我有如下表，我想改造它们。

year month week type count
2021  1     1    A    5
2021  1     1    B    6
2021  1     1    C    7
2021  1     2    A    0
2021  1     2    B    8
2021  1     2    C    9

我想像下面这样旋转。

year month week  A  B  C
2021  1     1    5  6  7
2021  1     2    0  8  9

我尝试了以下语句，但它返回了很多空列。 我想知道当添加新类型时我必须一一添加列。

select
    year,
    month,
    week,
    case when type in ('A') then count end as A,
    case when type in ('B') then count end as B,
    case when type in ('C') then count end as C,
    
from
   table

如果有人有意见，请告诉我。 谢谢

演示：dbfiddle

您可以使用FILTER clause:

SELECT
    year, month, week,
    MAX("count") FILTER (WHERE type = 'A') as A,   -- 2
    MAX("count") FILTER (WHERE type = 'B') as B,
    MAX("count") FILTER (WHERE type = 'C') as C
FROM mytable
GROUP BY year, month, week                         -- 1
ORDER BY year, month, week

或者你可以使用CASE clause:

SELECT
    year, month, week,
    MAX (CASE WHEN type = 'A' THEN "count" END) AS A,
    MAX (CASE WHEN type = 'B' THEN "count" END) AS B,
    MAX (CASE WHEN type = 'C' THEN "count" END) AS C
FROM mytable
GROUP BY year, month, week
ORDER BY year, month, week

1. 在这两种情况下，您都需要执行GROUP BY action.
2. 这使得聚合函数变得必要，例如MAX() or SUM()。最后你需要应用一种过滤器（CASE or FILTER) 仅聚合相关数据。

此外: 请注意这几个字count, year, month, week are SQL关键字。为了避免任何复杂化，您应该考虑其他列名称。