Remove duplicates from query

Question

I have the below table

item	area	qty
item 1	a	10
item 1	b	17
item 2	b	20
item 3	a	10
item 2	c	8

I am looking to have a result in SQL as below (a unique item and a unique area):

item	area a	area b	area c
item 1	10	17	0
item 2	0	20	8
item 3	10	0	0

i do have this query which not giving me what am looking for if the area has been changed or increased also its for 2 columns table not 3 columns:

select 
    item, 
    max(case when seqnum = 1 then area end) as area_1, 
    max(case when seqnum = 2 then area end) as area_2, 
    max(case when seqnum = 3 then area end) as area_3 
from (
    select A.*, 
        row_number() over (partition by item order by area) as seqnum 
    from A 
) A 
group by item;

Looking forwards to your kind help.

Answer 1

If you have a fixed list of areas, then no need for window functions ; you can explicitly filter on each individual value in max().

Another fix to your query is to take the max of qty rather than of area (whose value is already filtered).

select item, 
    coalesce(max(case when area = 'a' then qty end), 0) as area_a,
    coalesce(max(case when area = 'b' then qty end), 0) as area_b, 
    coalesce(max(case when area = 'c' then qty end), 0) as area_c
from mytable 
group by item