select 
    unioned.SURVEY_YEAR SV_YEAR,
    p.PF_NAME PREFECTURE,
    ek.KIND,
    unioned.AMT
from 
    (
        select
            SURVEY_YEAR,
            sum(ELEMENTARY) AMT,
            PF_CODE,
            1 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
        
        union all
        
        select
            SURVEY_YEAR,
            sum(MIDDLE) AMT,
            PF_CODE,
            2 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
        
        union all
        
        select
            SURVEY_YEAR,
            sum(HIGH) AMT,
            PF_CODE,
            3 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
        
        union all
        
        select
            SURVEY_YEAR,
            sum(JUNIOR_CLG) AMT,
            PF_CODE,
            4 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
        
        union all
        
        select
            SURVEY_YEAR,
            sum(COLLEGE) AMT,
            PF_CODE,
            5 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
        
        union all
        
        select
            SURVEY_YEAR,
            sum(GRADUATE) AMT,
            PF_CODE,
            6 KIND_NUM
        from ENROLLMENT_STATUS
        group by SURVEY_YEAR, PF_CODE
    ) unioned
    inner join PREFECTURE p on unioned.PF_CODE = p.PF_CODE
    inner join (
        select 1 as KIND_NUM, '小学校' as KIND
        union all
        select 2, '中学校'
        union all
        select 3, '高校'
        union all
        select 4, '短大'
        union all
        select 5, '大学'
        union all
        select 6, '大学院'
    ) ek on unioned.KIND_NUM = ek.KIND_NUM
where SURVEY_YEAR = 2020
    and AMT IS NOT NULL
order by unioned.PF_CODE, unioned.KIND_NUM