WITH SEQ_TBL AS ( 
    SELECT
        1 AS SEQ_NO 
    UNION ALL 
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
),
VERTICAL AS (
    select
        es.SURVEY_YEAR
        , p.PF_CODE
        , p.PF_NAME
        , st.SEQ_NO  -- 後でsort用
        , (
            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 0
            end
        ) AS AMT
    from ENROLLMENT_STATUS as es
    cross join SEQ_TBL as st
    inner join PREFECTURE as p
        on es.PF_CODE = p.PF_CODE
    where
        es.SURVEY_YEAR = "2020"

)

select
    SURVEY_YEAR AS SV_YEAR
    , PF_NAME AS PREFECTURE
    , KIND
    , SUM(AMT) AS AMT
from
    VERTICAL
where
    AMT IS NOT NULL
group by
    PF_NAME
    , KIND
order by
    PF_CODE
    ,SEQ_NO