with raw as (
          select SURVEY_YEAR, PF_CODE, '小学校' as KIND , sum(ELEMENTARY) as AMT, 1 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
UNION ALL select SURVEY_YEAR, PF_CODE, '中学校' as KIND , sum(MIDDLE    ) as AMT, 2 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
UNION ALL select SURVEY_YEAR, PF_CODE, '高校'   as KIND , sum(HIGH      ) as AMT, 3 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
UNION ALL select SURVEY_YEAR, PF_CODE, '短大'   as KIND , sum(JUNIOR_CLG) as AMT, 4 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
UNION ALL select SURVEY_YEAR, PF_CODE, '大学'   as KIND , sum(COLLEGE   ) as AMT, 5 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
UNION ALL select SURVEY_YEAR, PF_CODE, '大学院' as KIND , sum(GRADUATE  ) as AMT, 6 as KIND_ID from ENROLLMENT_STATUS group by SURVEY_YEAR, PF_CODE
)
select
    r.SURVEY_YEAR as SV_YEAR
  , p.PF_NAME     as PREFECTURE
  , r.KIND
  , r.AMT
from
           raw        r
inner join PREFECTURE p
        on r.PF_CODE = p.PF_CODE
where
    SURVEY_YEAR = 2020
and AMT         IS NOT NULL
order by
    r.PF_CODE asc
  , r.KIND_ID asc
;