WITH S_ELEMENTARY AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(ELEMENTARY) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_ELEMENTARY_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'小学校' AS KIND
        ,1 AS KIND_ID
    FROM
        S_ELEMENTARY
)
, S_MIDDLE AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(MIDDLE) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_MIDDLE_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'中学校' AS KIND
        ,2 AS KIND_ID
    FROM
        S_MIDDLE
)
, S_HIGH AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(HIGH) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_HIGH_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'高校' AS KIND
        ,3 AS KIND_ID
    FROM
        S_HIGH
)
, S_JUNIOR_CLG AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(JUNIOR_CLG) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_JUNIOR_CLG_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'短大' AS KIND
        ,4 AS KIND_ID
    FROM
        S_JUNIOR_CLG
)
, S_COLLEGE AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(COLLEGE) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_COLLEGE_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'大学' AS KIND
        ,5 AS KIND_ID
    FROM
        S_COLLEGE
)
, S_GRADUATE AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,SUM(GRADUATE) AS TOTAL
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR, PF_CODE
)
, S_GRADUATE_TOTAL AS 
(
    SELECT
        SURVEY_YEAR
        ,PF_CODE
        ,TOTAL
        ,'大学院' AS KIND
        ,6 AS KIND_ID
    FROM
        S_GRADUATE
)
, TOTAL_TABLE AS
(
SELECT
    *
FROM
    S_ELEMENTARY_TOTAL
UNION ALL
SELECT
    *
FROM
    S_MIDDLE_TOTAL
UNION ALL
SELECT
    *
FROM
    S_HIGH_TOTAL
UNION ALL
SELECT
    *
FROM
    S_JUNIOR_CLG_TOTAL
UNION ALL
SELECT
    *
FROM
    S_COLLEGE_TOTAL
UNION ALL
SELECT
    *
FROM
    S_GRADUATE_TOTAL AS SG
)
SELECT
    SURVEY_YEAR AS 'SV_YEAR'
    ,PF_NAME AS 'PREFECTURE'
    ,KIND AS 'KIND'
    -- ,KIND_ID
    ,TOTAL AS 'AMT'
FROM
    TOTAL_TABLE AS T
INNER JOIN
    PREFECTURE AS P
ON T.PF_CODE = P.PF_CODE
WHERE
    TOTAL IS NOT NULL
ORDER BY
    T.PF_CODE, KIND_ID