with ELEMENTARY as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '小学校' as KIND
    , ELEMENTARY AS AMT
    , 1 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and ELEMENTARY > 0
)
,MIDDLE as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '中学校' as KIND
    , MIDDLE AS AMT
    , 2 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and MIDDLE > 0
)
,HIGH as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '高校' as KIND
    , HIGH AS AMT
    , 3 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and HIGH > 0
)
,JUNIOR_CLG as (
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
)
,COLLEGE as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '大学' as KIND
    , COLLEGE AS AMT
    , 5 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and COLLEGE > 0
)
,GRADUATE as (
select
    SURVEY_YEAR
    ,PF_CODE
    , '大学院' as KIND
    , GRADUATE AS AMT
    , 6 as rn
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
    and GRADUATE > 0
)

select
    e.SURVEY_YEAR as SV_YEAR
    ,p.PF_NAME as PREFECTURE
    ,e.KIND as KIND
    ,sum(AMT) as AMT
from
    (
    select * from ELEMENTARY
    UNION ALL
    select * from MIDDLE
    UNION ALL
    select * from HIGH
    UNION ALL
    select * from JUNIOR_CLG
    UNION ALL
    select * from COLLEGE
    UNION ALL
    select * from GRADUATE
    ) as e
inner join PREFECTURE as p
    on e.PF_CODE = p.PF_CODE
group by e.SURVEY_YEAR,p.PF_NAME,e.KIND
order by e.PF_CODE asc , e.rn asc