with SEQ_TBL as (
    select
        1 as SEQ_NO
    union
    all
    select
        SEQ_NO + 1
    from
        SEQ_TBL
    where
        SEQ_NO < 6
),
SUB as(
    select
        ENROLLMENT_STATUS.SURVEY_YEAR as SV_YEAR,
        ENROLLMENT_STATUS.PF_CODE as PF_CODE,
        SEQ_TBL.SEQ_NO as SEQ_NO,
        case
            SEQ_TBL.SEQ_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
            SEQ_TBL.SEQ_NO
            when 1 then ENROLLMENT_STATUS.ELEMENTARY
            when 2 then ENROLLMENT_STATUS.MIDDLE
            when 3 then ENROLLMENT_STATUS.HIGH
            when 4 then ENROLLMENT_STATUS.JUNIOR_CLG
            when 5 then ENROLLMENT_STATUS.COLLEGE
            when 6 then ENROLLMENT_STATUS.GRADUATE
            else NULL
        end as AMOUNT
    from
        ENROLLMENT_STATUS
        cross join SEQ_TBL
    where
        SV_YEAR = 2020
)
select
    SV_YEAR,
    PF_NAME as PREFECTURE,
    KIND,
    sum(AMOUNT) as AMT
from
    PREFECTURE
    inner join SUB using(PF_CODE)
where
    AMOUNT is not NULL
group by
    SV_YEAR,
    PREFECTURE,
    KIND
order by
    PF_CODE,
    SEQ_NO;