SELECT
 x.SURVEY_YEAR as SV_YEAR
 ,p.PF_NAME as PREFECTURE
 ,(CASE
    WHEN x.s1 is not null THEN "小学校"
    WHEN x.s2 is not null THEN "中学校"
    WHEN x.s3 is not null THEN "高校"
    WHEN x.s4 is not null THEN "短大"
    WHEN x.s5 is not null THEN "大学"
    WHEN x.s6 is not null THEN "大学院"
   END) AS KIND
 ,(CASE
    WHEN x.s1 is not null THEN x.s1
    WHEN x.s2 is not null THEN x.s2
    WHEN x.s3 is not null THEN x.s3
    WHEN x.s4 is not null THEN x.s4
    WHEN x.s5 is not null THEN x.s5
    WHEN x.s6 is not null THEN x.s6
   END) AS AMT
FROM
 (
  select
   SURVEY_YEAR
   ,PF_CODE
   ,sum(ELEMENTARY) as s1
   ,sum(MIDDLE) as s2
   ,sum(HIGH) as s3
   ,sum(JUNIOR_CLG) as s4
   ,sum(COLLEGE) as s5
   ,sum(GRADUATE) as s6
  from ENROLLMENT_STATUS
  where SURVEY_YEAR=2020
  group by PF_CODE
  ) x
 INNER JOIN
 PREFECTURE p
 ON x.PF_CODE = p.PF_CODE
ORDER BY
 x.PF_CODE