-- 行に変換したい列数分、番号を生成
WITH SEQ_TBL AS (
    SELECT
        1 AS SEQ_NO 
    UNION ALL 
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
)
-- 縦持ちから横持ちへの変換
-- CROSS JOINすることで、例えば「2019/36000/10歳/ELEMENTARY/～」の行が6行に増幅される
-- -> CASE文でどの番号に対して何のデータを入れるかのラベル付けした列を作成
-- -> CASE文で意図番号の行に意図したデータが現れるようにした列を作成
-- --> 1行で横に持たれていたデータが、ここで縦6行に変換できたことになる
, test_1 AS (
    SELECT
        *
        , CASE ST.SEQ_NO 
            WHEN 1 THEN '小学校' 
            WHEN 2 THEN '中学校' 
            WHEN 3 THEN '高校' 
            WHEN 4 THEN '短大' 
            WHEN 5 THEN '大学' 
            WHEN 6 THEN '大学院' 
            ELSE NULL 
            END AS KIND
        , CASE ST.SEQ_NO 
            WHEN 1 THEN ES.ELEMENTARY 
            WHEN 2 THEN ES.MIDDLE 
            WHEN 3 THEN ES.HIGH 
            WHEN 4 THEN ES.JUNIOR_CLG 
            WHEN 5 THEN ES.COLLEGE 
            WHEN 6 THEN ES.GRADUATE 
            ELSE NULL 
            END AS AMOUNT 
    FROM
        ENROLLMENT_STATUS AS ES 
    CROSS JOIN
        SEQ_TBL AS ST 
    WHERE
        ES.SURVEY_YEAR = 2020
)
-- 横持にしたデータをGROUP BYしたいデータ毎に並べて確認
, test_2 AS (
    SELECT
        SURVEY_YEAR	
        , PF_CODE
        , SEQ_NO
        , KIND
        , AMOUNT
    FROM
        test_1
    ORDER BY
        PF_CODE ASC
        , SEQ_NO ASC
)
-- 都道府県、番号ごとにグループ化してほしい合計人数ををれぞれ出す
, test_3 AS (
    SELECT
        MAX(SURVEY_YEAR) AS SV_YEAR
        , PF_CODE
        , SEQ_NO
        , MAX(KIND) AS KIND
        , SUM(AMOUNT) AS AMT
    FROM
        test_2
    GROUP BY
        PF_CODE
        , SEQ_NO
)

SELECT
    test_3.SV_YEAR
    , PREFECTURE.PF_NAME AS PREFECTURE
    , test_3.KIND
    , test_3.AMT
FROM
    test_3
INNER JOIN
    PREFECTURE ON PREFECTURE.PF_CODE = test_3.PF_CODE
WHERE
    test_3.AMT IS NOT NULL
;