SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'小学校' AS "KIND"
   ,SUM(ELEMENTARY) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE
UNION ALL
SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'中学校' AS "KIND"
   ,SUM(MIDDLE) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE
UNION ALL
SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'高校' AS "KIND"
   ,SUM(HIGH) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE
UNION ALL
SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'短大' AS "KIND"
   ,SUM(JUNIOR_CLG) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE
UNION ALL
SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'大学' AS "KIND"
   ,SUM(COLLEGE) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE
UNION ALL
SELECT
    ES.SURVEY_YEAR AS "SV_YEAR"
   ,P.PF_NAME AS "PREFECTURE"
   ,'大学院' AS "KIND"
   ,SUM(GRADUATE) AS "AMT"
FROM
   ENROLLMENT_STATUS ES
INNER JOIN
   PREFECTURE P
ON
   ES.PF_CODE = P.PF_CODE
WHERE
   ES.SURVEY_YEAR = 2020
GROUP BY
   ES.PF_CODE