with org as (
select * from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
), org2 as (
select
    pf_code
    , '小学校' as kind
    , 1 as rank
    , sum(ELEMENTARY) as amt
from org
where ELEMENTARY is not null
group by PF_CODE
union
select
    pf_code
    , '中学校' as kind
    , 2 as rank
    , sum(MIDDLE) as amt
from org
where MIDDLE is not null
group by PF_CODE
union
select
    pf_code
    , '高学校' as kind
    , 3 as rank
    , sum(HIGH) as amt
from org
where HIGH is not null
group by PF_CODE
union
select
    pf_code
    , '短大' as kind
    , 4 as rank
    , sum(JUNIOR_CLG) as amt
from org
where JUNIOR_CLG is not null
group by PF_CODE
union
select
    pf_code
    , '大学' as kind
    , 5 as rank
    , sum(COLLEGE) as amt
from org
where COLLEGE is not null
group by PF_CODE
union
select
    pf_code
    , '大学院' as kind
    , 6 as rank
    , sum(GRADUATE) as amt
from org
where GRADUATE is not null
group by PF_CODE
)
select
    2020 as SV_YEAR
    , b.pf_name as PREFECTURE
    , a.kind as KIND
    , a.amt as AMT
from org2 a
inner join prefecture b
    on a.pf_code = b.pf_code
where a.amt is not null
order by a.pf_code, a.rank
;