with SEQ_TBL as(
    select
        1 as SEQ_NO
    union all
    select
        SEQ_NO + 1
    from
        SEQ_TBL
    where
        SEQ_NO < 6
    )
, KIND_TBL as(
    select
        SEQ_NO
        ,case SEQ_NO
        when 1 then '小学校'
        when 2 then '中学校'
        when 3 then '高校'
        when 4 then '短大'
        when 5 then '大学'
        when 6 then '大学院'
        end as KIND_NAME
    from
        SEQ_TBL
    )
, DATA_TBL as(
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,1 as SEQ_NO
        ,sum(ELEMENTARY) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    union
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,2 as SEQ_NO
        ,sum(MIDDLE) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    union
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,3 as SEQ_NO
        ,sum(HIGH) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    union
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,4 as SEQ_NO
        ,sum(JUNIOR_CLG) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    union
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,5 as SEQ_NO
        ,sum(COLLEGE) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    union
    select 
        SURVEY_YEAR
        ,PF_CODE
        ,6 as SEQ_NO
        ,sum(GRADUATE) as AMT
    from 
        ENROLLMENT_STATUS 
    where
        SURVEY_YEAR=2020
    group by
        SURVEY_YEAR, PF_CODE
    having
        AMT is not null
    )
select
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,KIND_NAME as KIND
    ,AMT
from
    DATA_TBL
    inner join PREFECTURE
    on PREFECTURE.PF_CODE=DATA_TBL.PF_CODE
    inner join KIND_TBL
    on KIND_TBL.SEQ_NO=DATA_TBL.SEQ_NO
order by
    DATA_TBL.PF_CODE asc,
    DATA_TBL.SEQ_NO asc
;