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
)
select
    e.SURVEY_YEAR as SV_YEAR
    ,p.PF_NAME as PREFECTURE
    ,e.KIND as KIND
    ,sum(AMT) as AMT
from
   tmp as e
left join PREFECTURE as p
    on e.PF_CODE = p.PF_CODE
group by SURVEY_YEAR,PF_NAME,KIND
order by e.PF_CODE asc , e.rn asc