WITH t AS (
  SELECT
    *
  FROM
    ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE USING(PF_CODE)
  WHERE
    SURVEY_YEAR = 2020
)
SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  KIND,
  SUM(AMT) AS AMT
FROM
  (
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "小学校" AS KIND,
      ELEMENTARY AS AMT,
      1 AS school_order
    FROM
      t
    UNION
    ALL
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "中学校" AS KIND,
      MIDDLE AS AMT,
      2 AS school_order
    FROM
      t
    UNION
    ALL
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "高校" AS KIND,
      HIGH AS AMT,
      3 AS school_order
    FROM
      t
    UNION
    ALL
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "短大" AS KIND,
      JUNIOR_CLG AS AMT,
      4 AS school_order
    FROM
      t
    UNION
    ALL
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "大学" AS KIND,
      COLLEGE AS AMT,
      5 AS school_order
    FROM
      t
    UNION
    ALL
    SELECT
      survey_year,
      PF_CODE,
      PF_NAME,
      "大学院" AS KIND,
      GRADUATE AS AMT,
      6 AS school_order
    FROM
      t
  )
WHERE AMT IS NOT NULL
GROUP BY
  SURVEY_YEAR,
  PF_NAME,
  KIND
ORDER BY
  PF_CODE,
  school_order