with SEQ_TBL as(
    select
        1 as SEQ_NO
    union
    select
        SEQ_NO + 1
    from
        SEQ_TBL
    where
        SEQ_NO < 6
)

,ES_SUB as(
    select
        ES.SURVEY_YEAR as SV_YEAR
        ,PR.PF_CODE as CODE
        ,PR.PF_NAME as PREFECTURE
        ,SEQ_NO
        ,case
            when SEQ_NO = 1 then "小学校"
            when SEQ_NO = 2 then "中学校"
            when SEQ_NO = 3 then "高校"
            when SEQ_NO = 4 then "短大"
            when SEQ_NO = 5 then "大学"
            when SEQ_NO = 6 then "大学院"
            else 0
            end
            as KIND
        ,case
            when SEQ_NO = 1 then ES.ELEMENTARY
            when SEQ_NO = 2 then ES.MIDDLE
            when SEQ_NO = 3 then ES.HIGH
            when SEQ_NO = 4 then ES.JUNIOR_CLG
            when SEQ_NO = 5 then ES.COLLEGE
            when SEQ_NO = 6 then ES.GRADUATE
            else 0
            end
            as AMT
    from
        ENROLLMENT_STATUS as ES
        inner join
            PREFECTURE as PR
            on ES.PF_CODE = PR.PF_CODE
        cross join
            SEQ_TBL
            
    where
        ES.SURVEY_YEAR = 2020

)


select
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,sum(AMT) as AMT
from
    ES_SUB
    
where
    AMT is not NULL
    
group by
    KIND
    ,PREFECTURE
    
order by
    CODE asc
    ,SEQ_NO asc
;