WITH data AS(
    SELECT *
    FROM PREFECTURE
    INNER JOIN ENROLLMENT_STATUS ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE SURVEY_YEAR = 2020
), merged AS (
SELECT
    PF_CODE,
    1 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '小学校' AS KIND,
    SUM(ELEMENTARY) AS AMT
FROM data
GROUP BY PREFECTURE

UNION ALL

SELECT
    PF_CODE,
    2 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '中学校' AS KIND,
    SUM(MIDDLE) AS AMT
FROM data
GROUP BY PREFECTURE

UNION ALL

SELECT
    PF_CODE,
    3 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '高校' AS KIND,
    SUM(HIGH) AS AMT
FROM data
GROUP BY PREFECTURE

UNION ALL

SELECT
    PF_CODE,
    4 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '短大' AS KIND,
    SUM(JUNIOR_CLG) AS AMT
FROM data
GROUP BY PREFECTURE

UNION ALL


SELECT
    PF_CODE,
    5 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学' AS KIND,
    SUM(COLLEGE) AS AMT
FROM data
GROUP BY PREFECTURE

UNION ALL


SELECT
    PF_CODE,
    6 AS RANK,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学院' AS KIND,
    SUM(GRADUATE) AS AMT
FROM data
GROUP BY PREFECTURE
)

SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM merged
WHERE AMT IS NOT NULL
ORDER BY PF_CODE ASC, RANK ASC;