with a as (
select
    SURVEY_YEAR ye,
    AREA_CODE ac,
    round(cast(FOOD_EXP as real)*100.0/ cast(CONSUMPTION_EXP as real),1) || '%' ratio
from HOUSEHOLD_SURVEY
group by SURVEY_YEAR,AREA_CODE)

select 
ac as CODE,
area_name as NAME,

max(case when ye=2022 then ratio else null end) as '2022_RATIO' , 
max(case when ye=2022 then rnk else null end) as '2022_RANK', 
max(case when ye=2017 then ratio else null end) as '2017_RATIO' , 
max(case when ye=2017 then rnk else null end) as '2017_RANK', 
max(case when ye=2012 then ratio else null end) as '2012_RATIO'  ,
max(case when ye=2012 then rnk else null end) as '2012_RANK'

 from
(
select 
    ye,
    ac,
    ratio,
    dense_rank() over(
        partition by ye order by ratio
    ) rnk
     from a
     ) a
     join area on area.area_code=a.ac
     group by ac
order by 3,1 desc