SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '小学校' AS KIND,
    ES.ELEMENTARY AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.ELEMENTARY IS NOT NULL

UNION

SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '中学校' AS KIND,
    ES.MIDDLE AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.MIDDLE IS NOT NULL

UNION

SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '高等学校' AS KIND,
    ES.HIGH AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.HIGH IS NOT NULL

UNION

SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '短大' AS KIND,
    ES.JUNIOR_CLG AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.JUNIOR_CLG IS NOT NULL

UNION

SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '大学' AS KIND,
    ES.COLLEGE AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.COLLEGE IS NOT NULL

UNION

SELECT 
    ES.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    '大学院' AS KIND,
    ES.GRADUATE AS AMT
FROM 
    ENROLLMENT_STATUS ES
JOIN 
    PREFECTURE P ON ES.PF_CODE = P.PF_CODE
WHERE 
    ES.SURVEY_YEAR = 2020 AND ES.GRADUATE IS NOT NULL

ORDER BY 
    PREFECTURE ASC, 
    KIND ASC;