CREATE TEMPORARY TABLE TMP (
    KIND_ID NUMBER(2)
    ,KIND VARCHAR(10)
);

INSERT INTO TMP(KIND_ID,KIND) VALUES(1,'小学校');
INSERT INTO TMP(KIND_ID,KIND) VALUES(2,'中学校');
INSERT INTO TMP(KIND_ID,KIND) VALUES(3,'高校');
INSERT INTO TMP(KIND_ID,KIND) VALUES(4,'短大');
INSERT INTO TMP(KIND_ID,KIND) VALUES(5,'大学');
INSERT INTO TMP(KIND_ID,KIND) VALUES(6,'大学院');


WITH PRE AS(
SELECT
    *
FROM PREFECTURE CROSS JOIN TMP)

,ELEMENTARY AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(ELEMENTARY) AS ELE_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

,MIDDLE AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(MIDDLE) AS MID_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

,HIGH AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(HIGH) AS HIG_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

,JUNIOR_CLG AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(JUNIOR_CLG) AS JUN_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

,COLLEGE AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(COLLEGE) AS COL_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

,GRADUATE AS(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(GRADUATE) AS GRAD_AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
)

SELECT
    SURVEY_YEAR AS SV_YEAR
    ,PF_NAME AS PREFECTURE
    ,KIND
    ,ELE_AMT AS AMT
FROM
(
SELECT *
FROM PRE NATURAL JOIN ELEMENTARY
WHERE KIND = '小学校'
UNION ALL
SELECT *
FROM PRE NATURAL JOIN MIDDLE
WHERE KIND = '中学校'
UNION ALL
SELECT *
FROM PRE NATURAL JOIN HIGH
WHERE KIND = '高校'
UNION ALL
SELECT *
FROM PRE NATURAL JOIN JUNIOR_CLG
WHERE KIND = '短大'
UNION ALL
SELECT *
FROM PRE NATURAL JOIN COLLEGE
WHERE KIND = '大学'
UNION ALL
SELECT *
FROM PRE NATURAL JOIN GRADUATE
WHERE KIND = '大学院'
)
WHERE ELE_AMT IS NOT NULL
ORDER BY PF_CODE, KIND_ID;