WITH TATE AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '小学校' AS KIND
    , SUM(ELEMENTARY)AS AMT
    , CAST(1 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
UNION ALL
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '中学校' AS KIND
    , SUM(MIDDLE)AS AMT
    , CAST(2 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
UNION ALL
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '高校' AS KIND
    , SUM(HIGH)AS AMT
    , CAST(3 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
UNION ALL
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '短大' AS KIND
    , SUM(JUNIOR_CLG)AS AMT
    , CAST(4 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
UNION ALL
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '大学' AS KIND
    , SUM(COLLEGE)AS AMT
    , CAST(5 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
UNION ALL
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '大学院' AS KIND
    , SUM(GRADUATE)AS AMT
    , CAST(6 AS INTEGER) as TYPE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
)
SELECT
    SURVEY_YEAR AS SV_YEAR
    , PF_NAME AS PREFECTURE
    , KIND
    , AMT
FROM
    TATE T
    INNER JOIN
        PREFECTURE P ON T.PF_CODE = P.PF_CODE
WHERE
    AMT IS NOT NULL
ORDER BY
    T.PF_CODE ASC
    , TYPE ASC
;