WITH AGGDATA AS (
  SELECT
    PF_CODE,
    SUM(ELEMENTARY) AS `ELEMENTARY`,
    SUM(MIDDLE) AS `MIDDLE`,
    SUM(HIGH) AS `HIGH`,
    SUM(JUNIOR_CLG) AS `JUNIOR_CLG`,
    SUM(COLLEGE) AS `COLLEGE`,
    SUM(GRADUATE) AS `GRADUATE`
  FROM
    ENROLLMENT_STATUS
  WHERE
    SURVEY_YEAR = 2020
  GROUP BY
    PF_CODE
), 
NAMEDATA AS (
  SELECT 1 AS `ID`, "ELEMENTARY" AS `EN`, "小学校" AS `JA`
  UNION SELECT 2, "MIDDLE", "中学校"
  UNION SELECT 3, "HIGH", "高校"
  UNION SELECT 4, "JUNIOR_CLG", "短大"
  UNION SELECT 5, "COLLEGE", "大学"
  UNION SELECT 6, "GRADUATE", "大学院"
), ANS AS (

SELECT
  2020 AS `SV_YEAR`,
  PRE.PF_NAME AS `PREFECTURE`,
  NAMEDATA.JA AS `KIND`,
  CASE
    WHEN NAMEDATA.ID = 1 THEN ELEMENTARY
    WHEN NAMEDATA.ID = 2 THEN MIDDLE
    WHEN NAMEDATA.ID = 3 THEN HIGH
    WHEN NAMEDATA.ID = 4 THEN JUNIOR_CLG
    WHEN NAMEDATA.ID = 5 THEN COLLEGE
    WHEN NAMEDATA.ID = 6 THEN GRADUATE
  ELSE NULL END AS `AMT`
FROM
  AGGDATA
  LEFT JOIN NAMEDATA ON 1 = 1
  LEFT JOIN PREFECTURE AS PRE ON AGGDATA.PF_CODE = PRE.PF_CODE
ORDER BY
  PRE.PF_CODE,
  NAMEDATA.ID

)

SELECT
  *
FROM
  ANS
WHERE
  AMT IS NOT NULL