WITH
A   AS(
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '小学校'            AS KIND
,   SUM(E.ELEMENTARY)   AS AMT
,   1                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
UNION ALL
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '中学校'            AS KIND
,   SUM(E.MIDDLE)       AS AMT
,   2                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
UNION ALL
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '高校'              AS KIND
,   SUM(E.HIGH)         AS AMT
,   3                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
UNION ALL
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '短大'              AS KIND
,   SUM(E.JUNIOR_CLG)   AS AMT
,   4                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
UNION ALL
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '大学'              AS KIND
,   SUM(E.COLLEGE)      AS AMT
,   5                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
UNION ALL
SELECT
    E.SURVEY_YEAR       AS SV_YEAR
,   E.PF_CODE
,   '大学院'            AS KIND
,   SUM(E.GRADUATE)     AS AMT
,   6                   AS RANK
FROM
    ENROLLMENT_STATUS   E
WHERE
    E.SURVEY_YEAR       = 2020
GROUP BY
    E.SURVEY_YEAR
,   E.PF_CODE
)
SELECT
    A.SV_YEAR
,   P.PF_NAME   AS PREFECTURE
,   A.KIND
,   A.AMT
FROM
    A,
    PREFECTURE P
WHERE
    A.PF_CODE   = P.PF_CODE
AND A.AMT       IS NOT NULL
ORDER BY
    A.PF_CODE
,   A.RANK