select
    vw.SURVEY_YEAR as SV_YEAR
,   pf.pf_name     as PREFECTURE
,   vw.kind        as KIND
,   SUM(vw.amt)    as AMT
from
(
    -- ELEMENTARY
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '小学校'      as kind
    ,   1             as kind_ord
    ,   es.ELEMENTARY as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.ELEMENTARY is not null
    union all
    -- MIDDLE
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '中学校'      as kind
    ,   2             as kind_ord
    ,   es.MIDDLE as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.MIDDLE is not null
    union all
    -- HIGH
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '高校'      as kind
    ,   3            as kind_ord
    ,   es.HIGH as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.HIGH is not null
    union all
    -- JUNIOR_CLG
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '短大'      as kind
    ,   4           as kind_ord
    ,   es.JUNIOR_CLG as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.JUNIOR_CLG is not null
    union all
    -- COLLEGE
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '大学'     as kind
    ,   5          as kind_ord
    ,   es.COLLEGE as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.COLLEGE is not null
    union all
    -- GRADUATE
    select 
        es.SURVEY_YEAR
    ,   es.PF_CODE
    ,   '大学院'    as kind
    ,   6           as kind_ord
    ,   es.GRADUATE as amt
    from
        ENROLLMENT_STATUS es
    where 
        es.GRADUATE is not null
)  vw
inner join
    PREFECTURE pf
on
    pf.pf_code = vw.pf_code
where
    vw.SURVEY_YEAR = 2020
group by
    vw.SURVEY_YEAR
,   pf.pf_name
,   vw.kind
order by
    pf.pf_code
,   vw.kind_ord