with s as(
    select 1 as num
    union all
    select num + 1
    from s
    where num < 6
)

select 
    sub.SURVEY_YEAR as SV_YEAR,
    p.PF_NAME as PREFECTURE,
    sub.KIND as KIND,
    sum(sub.AMOUNT) as AMT
from (
    select 
        s1.SURVEY_YEAR as SURVEY_YEAR,
        s1.PF_CODE as PF_CODE,
        s.num as NUM,
        case s.num
            WHEN 1 THEN '小学校' 
            WHEN 2 THEN '中学校' 
            WHEN 3 THEN '高校' 
            WHEN 4 THEN '短大' 
            WHEN 5 THEN '大学' 
            WHEN 6 THEN '大学院' 
            ELSE NULL 
            END AS KIND,
        CASE s.num
                WHEN 1 THEN s1.ELEMENTARY 
                WHEN 2 THEN s1.MIDDLE 
                WHEN 3 THEN s1.HIGH 
                WHEN 4 THEN s1.JUNIOR_CLG 
                WHEN 5 THEN s1.COLLEGE 
                WHEN 6 THEN s1.GRADUATE 
                ELSE NULL 
                END AS AMOUNT 
    from ENROLLMENT_STATUS s1
    cross join s
    where s1.SURVEY_YEAR = 2020
) sub
join PREFECTURE p on p.PF_CODE = sub.PF_CODE
where sub.AMOUNT is not null
group by p.PF_NAME, sub.KIND, sub.SURVEY_YEAR
order by sub.PF_CODE,sub.NUM