with t0 as (
    select 
        SURVEY_YEAR,
        PF_CODE,
        '小学校' as KIND,
        ELEMENTARY as AMT,
        1 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    union all
    select 
        SURVEY_YEAR,
        PF_CODE,
        '中学校' as KIND,
        MIDDLE as AMT,
        2 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    union all
    select 
        SURVEY_YEAR,
        PF_CODE,
        '高校' as KIND,
        HIGH as AMT,
        3 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    union all
    select 
        SURVEY_YEAR,
        PF_CODE,
        '短大' as KIND,
        JUNIOR_CLG as AMT,
        4 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    union all
    select 
        SURVEY_YEAR,
        PF_CODE,
        '大学' as KIND,
        COLLEGE	 as AMT,
        5 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    union all
    select 
        SURVEY_YEAR,
        PF_CODE,
        '大学院' as KIND,
        GRADUATE	 as AMT,
        6 as seq
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    
), t1 as (
    select SURVEY_YEAR,PF_CODE, KIND, seq, SUM(AMT) as AMT
    from t0 group by 1, 2, 3, 4
)

select
    t1.SURVEY_YEAR as SV_YEAR,
    p.PF_NAME as PREFECTURE,
    t1.KIND,
    t1.AMT
from t1 inner join PREFECTURE as p on t1.PF_CODE = p.PF_CODE
where t1.AMT is not null
order by t1.PF_CODE ,t1.seq