select
    SURVEY_YEAR as SV_YEAR,
    PF_NAME as PREFECTURE,
    KIND,
    AMT
from
    (
        select
            *
        from
            PREFECTURE p
            join
                (
                    select
                        SURVEY_YEAR,
                        PF_CODE,
                        sum(ELEMENTARY) AS AMT,
                        '小学校' as KIND,
                        1 as TYPE
                    from
                        ENROLLMENT_STATUS
                    where
                        SURVEY_YEAR = 2020
                    group by
                        SURVEY_YEAR,
                        PF_CODE
                ) k1
            on  p.PF_CODE = k1.PF_CODE
            and k1.AMT is not null
        union
        select
            *
        from
            PREFECTURE p
        join
            (
                select
                    SURVEY_YEAR,
                    PF_CODE,
                    sum(MIDDLE) AS AMT,
                    '中学校' as KIND,
                    2 as TYPE
                from
                    ENROLLMENT_STATUS
                where
                    SURVEY_YEAR = 2020
                group by
                    SURVEY_YEAR,
                    PF_CODE
            ) k2
        on  p.PF_CODE = k2.PF_CODE
        and k2.AMT is not null
        union
        select
            *
        from
            PREFECTURE p
        join
            (
                select
                    SURVEY_YEAR,
                    PF_CODE,
                    sum(HIGH) AS AMT,
                    '高校' as KIND,
                    3 as TYPE
                from
                    ENROLLMENT_STATUS
                where
                    SURVEY_YEAR = 2020
                group by
                    SURVEY_YEAR,
                    PF_CODE
            ) k3
        on  p.PF_CODE = k3.PF_CODE
        and k3.AMT is not null
        union
        select
            *
        from
            PREFECTURE p
        join
            (
                select
                    SURVEY_YEAR,
                    PF_CODE,
                    sum(JUNIOR_CLG) AS AMT,
                    '短大' as KIND,
                    4 as TYPE
                from
                    ENROLLMENT_STATUS
                where
                    SURVEY_YEAR = 2020
                group by
                    SURVEY_YEAR,
                    PF_CODE
            ) k4
        on  p.PF_CODE = k4.PF_CODE
        and k4.AMT is not null
        union
        select
            *
        from
            PREFECTURE p
        join
            (
                select
                    SURVEY_YEAR,
                    PF_CODE,
                    sum(COLLEGE) AS AMT,
                    '大学' as KIND,
                    5 as TYPE
                from
                    ENROLLMENT_STATUS
                where
                    SURVEY_YEAR = 2020
                group by
                    SURVEY_YEAR,
                    PF_CODE
            ) k5
        on  p.PF_CODE = k5.PF_CODE
        and k5.AMT is not null
        union
        select
            *
        from
            PREFECTURE p
        join
            (
                select
                    SURVEY_YEAR,
                    PF_CODE,
                    sum(GRADUATE) AS AMT,
                    '大学院' as KIND,
                    6 as TYPE
                from
                    ENROLLMENT_STATUS
                where
                    SURVEY_YEAR = 2020
                group by
                    SURVEY_YEAR,
                    PF_CODE
            ) k6
        on  p.PF_CODE = k6.PF_CODE
        and k6.AMT is not null
    )
order by
    PF_CODE ASC,
    TYPE ASC