i have the below table

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

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

i try to modify the existing query, looking for help to modify and undestand what i was missing to have more knowlage for the furture

CodePudding user response：

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