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
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
GROUP BY action.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.