select es.SURVEY_YEAR as SV_YEAR
       , p.PF_NAME as PREFECTURE
       , es.kind as KIND
       , coalesce(es.kinde,0) as AMT
from( select survey_year
             , pf_code
             , '小学校' as kind
             , sum(ELEMENTARY) as kinde
             , 1 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a
        union all
      select survey_year
             , pf_code
             , '中学校' as kind
             , sum(MIDDLE) as kinde
             , 2 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a
        union all
      select survey_year
             , pf_code
             , '高校' as kind
             , sum(HIGH) as kinde
             , 3 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a
        union all
      select survey_year
             , pf_code
             , '短大' as kind
             , sum(JUNIOR_CLG) as kinde
             , 4 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a
        union all
      select survey_year
             , pf_code
             , '大学' as kind
             , sum(COLLEGE) as kinde
             , 5 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a
        union all
      select survey_year
             , pf_code
             , '大学院' as kind
             , sum(GRADUATE) as kinde
             , 6 as a
        from ENROLLMENT_STATUS
        where survey_year = '2020'
        group by survey_year
             , pf_code
             , kind
             , a) as es
        join PREFECTURE as p
        on p.pf_code = es.pf_code
        and AMT <> 0
       order by es.pf_code, es.a;