WITH SEQNO(NO) AS(
    SELECT 1
    UNION ALL
    SELECT NO+1
    FROM SEQNO
    WHERE NO<6
    ),
    
REPORT AS(
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        NO,
        CASE 
            WHEN NO=1 THEN '小学校'
            WHEN NO=2 THEN '中学校'
            WHEN NO=3 THEN '高校'
            WHEN NO=4 THEN '短大'
            WHEN NO=5 THEN '大学'
            WHEN NO=6 THEN '大学院'
        END AS KIND
    FROM (
        SELECT PF_CODE, SURVEY_YEAR
        FROM ENROLLMENT_STATUS
        WHERE SURVEY_YEAR=2020
        GROUP BY PF_CODE
        )
        CROSS JOIN SEQNO
    ),

SUM_AMT AS(
    SELECT
        PF_CODE,
        SUM(ELEMENTARY) AS SUME,
        SUM(MIDDLE) AS SUMM,
        SUM(HIGH) AS SUMH,
        SUM(JUNIOR_CLG) AS SUMJ,
        SUM(COLLEGE) AS SUMC,
        SUM(GRADUATE) AS SUMG
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR=2020
    GROUP BY
        PF_CODE
    )
    
SELECT
    SV_YEAR,
    PF_NAME,
    R.KIND,
    CASE
        WHEN KIND='小学校' THEN S.SUME
        WHEN KIND='中学校' THEN S.SUMM
        WHEN KIND='高校' THEN S.SUMH
        WHEN KIND='短大' THEN S.SUMJ
        WHEN KIND='大学' THEN S.SUMC
        WHEN KIND='大学院' THEN S.SUMG
    END AS AMT
FROM
    REPORT AS R
    INNER JOIN SUM_AMT AS S
        ON R.PF_CODE=S.PF_CODE
    INNER JOIN PREFECTURE AS P
        ON R.PF_CODE=P.PF_CODE
WHERE
    AMT IS NOT NULL
ORDER BY
    R.PF_CODE,R.NO
;