with DIVIDED as (
    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
),
SCHOOL_KIND as (
    select 1 as KIND_NUM, '小学校' as KIND_NAME
    union all
    select 2, '中学校'
    union all
    select 3, '高校'
    union all
    select 4, '短大'
    union all
    select 5, '大学'
    union all
    select 6, '大学院'
)
select 
    d.SURVEY_YEAR SV_YEAR,
    p.PF_NAME PREFECTURE,
    sk.KIND_NAME KIND,
    d.AMT
from 
    DIVIDED d
    inner join PREFECTURE p on d.PF_CODE = p.PF_CODE
    inner join SCHOOL_KIND sk on d.KIND_NUM = sk.KIND_NUM
where SURVEY_YEAR = 2020
    and AMT IS NOT NULL
order by d.PF_CODE, d.KIND_NUM