select
    SV_YEAR,
    PREFECTURE,
    case
        when KIND_NUM == 0 then "小学校"
        when KIND_NUM == 1 then "中学校"
        when KIND_NUM == 2 then "高校"
        when KIND_NUM == 3 then "短大"
        when KIND_NUM == 4 then "大学"
        when KIND_NUM == 5 then "大学院"
        else "a"
    end as KIND,
    AMT
from
    (
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            0 as KIND_NUM,
            sum(E.ELEMENTARY) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
        union
        all
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            1 as KIND_NUM,
            sum(E.MIDDLE) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
        union
        all
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            2 as KIND_NUM,
            sum(E.HIGH) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
        union
        all
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            3 as KIND_NUM,
            sum(E.JUNIOR_CLG) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
        union
        all
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            4 as KIND_NUM,
            sum(E.COLLEGE) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
        union
        all
        select
            P.PF_CODE as PF_CODE,
            E.SURVEY_YEAR as SV_YEAR,
            P.PF_NAME as PREFECTURE,
            5 as KIND_NUM,
            sum(E.GRADUATE) as AMT
        from
            ENROLLMENT_STATUS as E,
            PREFECTURE as P
        where
            P.PF_CODE == E.PF_CODE
            and E.SURVEY_YEAR == 2020
        group by
            SV_YEAR,
            PREFECTURE
    )
where
    AMT is not null
order by
    PF_CODE,
    KIND_NUM