WITH S_TABLE as (
    select 
        1 AS S_NO
    union all
    select 
        S_NO + 1 
    from 
        S_TABLE 
    where 
        S_NO < 6
)
select NEW.SURVEY_YEAR AS SV_YEAR
    ,PF.PF_NAME AS PREFECTURE
    ,NEW.SCHOOL AS KIND
    ,sum(NEW.EN_SCHOOL) AS AMT
from (
    select 
        ES.SURVEY_YEAR
        , ES.PF_CODE
        , ST.S_NO
        , case ST.S_NO
            when 1 then "小学校"
            when 2 then "中学校"
            when 3 then "高校"
            when 4 then "短大"
            when 5 then "大学"
            when 6 then "大学院"
            else null end as SCHOOL
        ,case ST.S_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 EN_SCHOOL
    from
        ENROLLMENT_STATUS AS ES
        cross join S_TABLE AS ST
    where ES.SURVEY_YEAR = 2020
) AS NEW
inner join PREFECTURE AS PF
        on PF.PF_CODE = NEW.PF_CODE
where NEW.EN_SCHOOL is not null
group by NEW.SURVEY_YEAR,PF.PF_NAME,NEW.SCHOOL
order by PF.PF_CODE ,NEW.S_NO