select
    SV_YEAR,
    PREFECTURE,
    AMT,
    KIND
from
(  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.ELEMENTARY) as AMT,
    '小学校' as KIND,
    1 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.ELEMENTARY is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.MIDDLE) as AMT,
    '中学校' as KIND,
    2 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.MIDDLE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.HIGH) as AMT,
    '高校' as KIND,
    3 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.HIGH is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.JUNIOR_CLG) as AMT,
    '短大' as KIND,
    4 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.JUNIOR_CLG is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.COLLEGE) as AMT,
    '大学' as KIND,
    5 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.COLLEGE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.GRADUATE) as AMT,
    '大学院' as KIND,
    6 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.GRADUATE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
)
order by
  PREFECTURE asc,
  NUM asc