with org as (
select * from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
    -- and PF_CODE = '36000'
-- order by age
), org2 as (
select
    pf_code
    , '小学校' as kind
    , 1 as rank
    , sum(case when ELEMENTARY is not null then ELEMENTARY else 0 end) as amt
from org
group by PF_CODE
union all
select
    pf_code
    , '中学校' as kind
    , 2 as rank
    , sum(case when MIDDLE is not null then MIDDLE else 0 end) as amt
from org
group by PF_CODE
union all
select
    pf_code
    , '高学校' as kind
    , 3 as rank
    , sum(case when HIGH is not null then HIGH else 0 end) as amt
from org
group by PF_CODE
union all
select
    pf_code
    , '短大' as kind
    , 4 as rank
    , sum(case when JUNIOR_CLG is not null then JUNIOR_CLG else 0 end) as amt
from org
group by PF_CODE
union all
select
    pf_code
    , '大学' as kind
    , 5 as rank
    , sum(case when COLLEGE is not null then COLLEGE else 0 end) as amt
from org
group by PF_CODE
union all
select
    pf_code
    , '大学院' as kind
    , 6 as rank
    , sum(case when GRADUATE is not null then GRADUATE else 0 end) as amt
from org
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 amt > 0
order by 1, rank
;