with
    --2020年都道府県ごとの集約
    summary as (
    select SURVEY_YEAR as "SV_YEAR",
            PF_CODE,
            sum(ELEMENTARY) as "小学校",
            sum(MIDDLE) as "中学校",
            sum(HIGH) as "高校",
            sum(JUNIOR_CLG) as "短大",
            sum(COLLEGE) as "大学",
            sum(GRADUATE) as "大学院"
    from ENROLLMENT_STATUS
    where SURVEY_YEAR = 2020
    group by 1,2
    ),
    
    --表側用
    kind_school as (
    select '小学校' as "KIND",
            1 as "表示順"
    union
    select '中学校' as "KIND",
            2 as "表示順"
    union
    select '高校' as "KIND",
            3 as "表示順"
    union
    select '短大' as "KIND",
            4 as "表示順"
    union
    select '大学' as "KIND",
            5 as "表示順"
    union
    select '大学院' as "KIND",
            6 as "表示順"
    ),
    
    --表側追加
    base as (
    select *
    from summary
        cross join 
        kind_school
    ),
    
    --縦横変換
    convert as (
    select SV_YEAR,
            PF_CODE,
            KIND,
            case KIND when '小学校' then 小学校 
                      when '中学校' then 中学校 
                      when '高校' then 高校
                      when '短大' then 短大
                      when '大学' then 大学
                      when '大学院' then 大学院
                      end as "AMT",
            "表示順"
    from base
    where "AMT" IS NOT NULL
    )

select SV_YEAR,
        PF_NAME as "PREFECTURE",
        KIND,
        AMT
from convert
    inner join PREFECTURE
    using(PF_CODE)
order by PF_CODE asc,
        "表示順" asc
;