with tmp as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '小学校' as KIND
    , ELEMENTARY AS AMT
    , 1 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and ELEMENTARY > 0
    and ELEMENTARY is not null
UNION ALL
select
    SURVEY_YEAR
    ,PF_CODE
    , '中学校' as KIND
    , MIDDLE AS AMT
    , 2 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and MIDDLE > 0
    and MIDDLE is not null
UNION ALL
select
    SURVEY_YEAR
    ,PF_CODE
    , '高校' as KIND
    , HIGH AS AMT
    , 3 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and HIGH > 0
    and HIGH is not null
UNION ALL
select
    SURVEY_YEAR
    ,PF_CODE
    , '短大' as KIND
    , JUNIOR_CLG AS AMT
    , 4 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and JUNIOR_CLG > 0
    and JUNIOR_CLG is not null
UNION ALL
select
    SURVEY_YEAR
    ,PF_CODE
    , '大学' as KIND
    , COLLEGE AS AMT
    , 5 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and COLLEGE > 0
    and COLLEGE is not null
UNION ALL
select
    SURVEY_YEAR
    ,PF_CODE
    , '大学院' as KIND
    , GRADUATE AS AMT
    , 6 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and GRADUATE > 0
    and GRADUATE is not null
), t as (
    select
        e.SURVEY_YEAR as SV_YEAR
        ,e.PF_CODE
        ,p.PF_NAME as PREFECTURE
        ,e.KIND as KIND
        ,e.rn
        ,sum(AMT) as AMT
    from
       tmp as e
    inner join PREFECTURE as p
        on e.PF_CODE = p.PF_CODE
    group by SURVEY_YEAR,PF_NAME,KIND,e.rn
)
select
    SV_YEAR
    ,PREFECTURE AS p
    ,KIND
    ,AMT
from t
order by PF_CODE asc, rn asc