with ENROLLMENT_STATUS_VERTICAL as (
    select
          SURVEY_YEAR
        , PF_CODE
        , PF_NAME
        , sum(ELEMENTARY)     as ELEMENTARY
        , sum(MIDDLE)         as MIDDLE   
        , sum(HIGH)           as HIGH
        , sum(JUNIOR_CLG)     as JUNIOR_CLG
        , sum(COLLEGE)        as COLLEGE  
        , sum(GRADUATE)       as GRADUATE
    from
        (
        select
            ENROLLMENT_STATUS.SURVEY_YEAR
            , ENROLLMENT_STATUS.PF_CODE
            , PREFECTURE.PF_NAME
            , ENROLLMENT_STATUS.AGE
            , ENROLLMENT_STATUS.ELEMENTARY
            , ENROLLMENT_STATUS.MIDDLE   
            , ENROLLMENT_STATUS.HIGH
            , ENROLLMENT_STATUS.JUNIOR_CLG
            , ENROLLMENT_STATUS.COLLEGE  
            , ENROLLMENT_STATUS.GRADUATE
        from ENROLLMENT_STATUS
            left outer join PREFECTURE using(PF_CODE)
        where
            ENROLLMENT_STATUS.SURVEY_YEAR = 2020
        )
    group by 
        SURVEY_YEAR
        , PF_CODE
        , PF_NAME
)

select
      SURVEY_YEAR as SV_YEAR
    , PF_NAME as PREFECTURE
    , KIND
    , AMT
from
    (
    select SURVEY_YEAR, PF_CODE, PF_NAME, '小学校' as KIND, ELEMENTARY as AMT, 1 as SEQ from ENROLLMENT_STATUS_VERTICAL union all
    select SURVEY_YEAR, PF_CODE, PF_NAME, '中学校' as KIND, MIDDLE     as AMT, 2 as SEQ from ENROLLMENT_STATUS_VERTICAL union all
    select SURVEY_YEAR, PF_CODE, PF_NAME, '高校'   as KIND, HIGH       as AMT, 3 as SEQ from ENROLLMENT_STATUS_VERTICAL union all
    select SURVEY_YEAR, PF_CODE, PF_NAME, '短大'   as KIND, JUNIOR_CLG as AMT, 4 as SEQ from ENROLLMENT_STATUS_VERTICAL union all
    select SURVEY_YEAR, PF_CODE, PF_NAME, '大学'   as KIND, COLLEGE    as AMT, 5 as SEQ from ENROLLMENT_STATUS_VERTICAL union all
    select SURVEY_YEAR, PF_CODE, PF_NAME, '大学院' as KIND, GRADUATE   as AMT, 6 as SEQ from ENROLLMENT_STATUS_VERTICAL
    )
where
    AMT is not null
ORDER BY PF_CODE asc, SEQ asc