with tbl as ( 
    select
        1 AS sql_no 
    union all 
    select
        sql_no + 1 
    from
        tbl 
    where
        sql_no < 6
),
es_sub as (
    select
        es.SURVEY_YEAR
        ,es.PF_CODE
        ,st.sql_no
        ,case st.sql_no 
            when 1 then '小学校' 
            when 2 then '中学校' 
            when 3 then '高校' 
            when 4 then '短大' 
            when 5 then '大学' 
            when 6 then '大学院' 
            else null 
            end as kind
        ,case st.sql_no 
            when 1 then es.ELEMENTARY 
            when 2 then es.MIDDLE 
            when 3 then es.HIGH 
            when 4 then es.JUNIOR_CLG 
            when 5 then es.COLLEGE 
            when 6 then es.GRADUATE 
            else null 
            end as amount
    from
        ENROLLMENT_STATUS as es 
        cross join tbl as st
    where
        es.SURVEY_YEAR = 2020
)
select
    es_sub.SURVEY_YEAR as SV_YEAR
    ,pf.PF_NAME as PREFECTURE
    ,es_sub.kind as KIND
    ,sum(es_sub.amount) as AMT 
from
    es_sub 
    inner join PREFECTURE as pf 
    on es_sub.PF_CODE = pf.PF_CODE 
where
    es_sub.amount is not null 
group by
    es_sub.SURVEY_YEAR
    ,pf.PF_NAME
    ,es_sub.kind 
order by
    es_sub.PF_CODE
    ,es_sub.sql_no
;