select
    SURVEY_YEAR as SV_YEAR
    , PF_NAME as PREFECTURE
    , KIND
    , sum(AMT) as AMT

from 
(    select
        SURVEY_YEAR
        , PF_CODE
        , '小学校' as KIND
        , 1 as hoge
        , ELEMENTARY as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and ELEMENTARY is not NULL
    
    union all
    
    select
        SURVEY_YEAR
        , PF_CODE
        , '中学校' as KIND
        , 2 as hoge
        , MIDDLE as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and MIDDLE is not NULL

    union all
    
    select
        SURVEY_YEAR
        , PF_CODE
        , '高校' as KIND
        , 3 as hoge
        , HIGH as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and HIGH is not NULL
    
    union all
    
    select
        SURVEY_YEAR
        , PF_CODE
        , '短大' as KIND
        , 4 as hoge
        , JUNIOR_CLG as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and JUNIOR_CLG is not NULL
    
     union all
    
    select
        SURVEY_YEAR
        , PF_CODE
        , '大学' as KIND
        , 5 as hoge
        , COLLEGE as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and COLLEGE is not NULL
    
     union all
    
    select
        SURVEY_YEAR
        , PF_CODE
        , '大学院' as KIND
        , 6 as hoge
        , GRADUATE as AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020 and GRADUATE is not NULL
) inner join PREFECTURE
    using(PF_CODE)

group by 1, 2, 3
order by 
 PF_CODE, hoge