select 
    CODE
    , NAME
    , sum(case when year=2022 then rank end) as '2022_RANK'
    , sum(case when year=2022 then ratio end) || '%' as '2022_RATIO'
    , sum(case when year=2017 then rank end) as '2017_RANK'
    , sum(case when year=2017 then ratio end) || '%' as '2017_RATIO'
    , sum(case when year=2022 then rank end) as '2012_RANK'
    , sum(case when year=2022 then ratio end) || '%' as '2012_RATIO'
from (
    select
        h.AREA_CODE as CODE
        , AREA_NAME as NAME
        , SURVEY_YEAR as year
        , round(100.0 * FOOD_EXP / CONSUMPTION_EXP, 1) as ratio
        , dense_rank() over(partition by SURVEY_YEAR order by round((100.0 * FOOD_EXP / CONSUMPTION_EXP), 1)) as rank
    from 
        HOUSEHOLD_SURVEY h
        left outer join
            AREA a
            on h.AREA_CODE = a.AREA_CODE
    group by
        h.AREA_CODE
        , AREA_NAME
        , SURVEY_YEAR
) t    
group by 
    CODE
    , NAME
order by
    sum(case when year=2022 then rank end)
    , CODE desc
;