-- 都道府県毎に就学状況の人数を集計した就学状況データがある。
-- 都道府県、年齢別に就学先が横持ちで人数集計されているが、表示例のように都道府県、
-- 就学先で1レコードとなるように集計して縦持ちのデータに変換しなさい。 
-- また、集計対象とするのは、調査年が2020年のデータのみとし、KINDに表示する文言は、
-- 就学先の論理名とする。ただし、集計値がNULLのデータは表示対象としない。
-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)

-- SURVEY_YEAR → SV_YEAR
-- PF_NAME → PREFECTURE
-- 集計した就学先の種類 → KIND
-- 就学先ごとに集計した人数 → AMT
-- 表示順

-- 都道府県コードの昇順
-- 就学状況データの就学先を左から並べた順番（小学校→・・→大学院）

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