WITH ELEMENTARY_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '小学校' AS KIND
    , SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , ELEMENTARY
) 
, MIDDLE_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '中学校' AS KIND
    , SUM(MIDDLE) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , MIDDLE
)  
, HIGH_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '高校' AS KIND
    , SUM(HIGH) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , HIGH
)  
, JUNIOR_CLG_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '短大' AS KIND
    , SUM(JUNIOR_CLG) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , JUNIOR_CLG
) 
, COLLEGE_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '大学' AS KIND
    , SUM(COLLEGE) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , COLLEGE
) 
, GRADUATE_COUNT AS (
SELECT
    SURVEY_YEAR
    , PF_CODE
    , '大学院' AS KIND
    , SUM(GRADUATE) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
    , GRADUATE
) 
SELECT
    2020 AS SV_YEAR
    , PF_CODE
    , KIND
    , AMT
FROM
    ELEMENTARY_COUNT
    , MIDDLE_COUNT
    , HIGH_COUNT
    , JUNIOR_CLG_COUNT
    , COLLEGE_COUNT
    , GRADUATE_COUNT
;