SELECT
  a.survey_year AS "SV_YEAR",
  b.pf_name AS "PREFECTURE",
  a.kind AS "KIND",
  a.amt AS "AMT"
FROM
  (
    SELECT
      survey_year,
      pf_code,
      sum(elementary) AS amt,
      '小学校' AS "kind",
      1 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
    UNION ALL
    SELECT
      survey_year,
      pf_code,
      sum(middle),
      '中学校' AS "kind",
      2 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
    UNION ALL
    SELECT
      survey_year,
      pf_code,
      sum(high),
      '高校' AS "kind",
      3 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
    UNION ALL
    SELECT
      survey_year,
      pf_code,
      sum(junior_clg),
      '短大' AS "kind",
      4 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
    UNION ALL
    SELECT
      survey_year,
      pf_code,
      sum(college),
      '大学' AS "kind",
      5 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
    UNION ALL
    SELECT
      survey_year,
      pf_code,
      sum(graduate),
      '大学院' AS "kind",
      6 AS "seq"
    FROM
      enrollment_status
    WHERE
      survey_year = 2020
    GROUP BY
      pf_code
  ) a
  JOIN prefecture b ON a.pf_code = b.pf_code
WHERE
  a.amt IS NOT NULL
ORDER BY
  a.pf_code,
  a."seq";