with tmp as (
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,0 as "KIND_NO"
    ,'小学校' as "KIND"
    ,sum(e.elementary) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.elementary is not null
group by
    e.survey_year, p.pf_code, p.pf_name
union all
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,1 as "KIND_NO"
    ,'中学校' as "KIND"
    ,sum(e.middle) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.middle is not null
group by
    e.survey_year, p.pf_code, p.pf_name
union all
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,2 as "KIND_NO"
    ,'高校' as "KIND"
    ,sum(e.high) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.high is not null
group by
    e.survey_year, p.pf_code, p.pf_name
union all
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,3 as "KIND_NO"
    ,'短大' as "KIND"
    ,sum(e.junior_clg) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.junior_clg is not null
group by
    e.survey_year, p.pf_code, p.pf_name
union all
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,4 as "KIND_NO"
    ,'大学' as "KIND"
    ,sum(e.college) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.college is not null
group by
    e.survey_year, p.pf_code, p.pf_name
union all
select
    e.survey_year as "SV_YEAR"
    ,p.pf_code as "CODE"
    ,p.pf_name as "PREFECTURE"
    ,5 as "KIND_NO"
    ,'大学院' as "KIND"
    ,sum(e.graduate) as "AMT"
from
    enrollment_status e left join prefecture p
    on e.pf_code = p.pf_code
where
    e.survey_year = 2020
    and e.graduate is not null
group by
    e.survey_year, p.pf_code, p.pf_name
)
select
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,AMT
from
    tmp
order by
    code, kind_no
;