SELECT
2020 SV_YEAR,
PF.PF_NAME PREFECTURE,
ES.KIND KIND,
SUM(ES.AMT) AMT

FROM
PREFECTURE PF

JOIN (
SELECT
1 OD, '小学校' KIND, ELEMENTARY AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2020

UNION

SELECT
2 OD, '中学校' KIND, MIDDLE AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2020

UNION

SELECT
3 OD, '高校' KIND, HIGH AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2022

UNION

SELECT
4 OD, '短大' KIND, JUNIOR_CLG AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2020

UNION

SELECT
5 OD, '大学' KIND, COLLEGE AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2020

UNION

SELECT
6 OD, '大学院' KIND, GRADUATE AMT, PF_CODE
FROM
ENROLLMENT_STATUS
WHERE
SURVEY_YEAR = 2020
) ES
ON
PF.PF_CODE = ES.PF_CODE

GROUP BY
PF.PF_CODE,
ES.KIND

HAVING
SUM(ES.AMT) IS NOT NULL

ORDER BY
PF.PF_CODE,
ES.OD