How to pivot in postgresql

Question

I have table like following,and I would like to transform them.

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

I'd like to pivot like following.

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

I tried like following statement, but it returned a lot of null columns. And I wonder I must add columns one by one when new type will be added.

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

If someone has opinion, please let me know. Thanks

Answer 1

demo: db<>fiddle

You can either use the 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

or you can use the 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. In both cases you need to perform a GROUP BY action.
2. This makes an aggregation function necessary, like MAX() or SUM(). Finally you need to apply a kind of filter (CASE or FILTER) to only aggregate the related data.

Additionally: Please note that the words count, year, month, week are keywords of SQL. To avoid any complications you should think about other column names.