select
    SURVEY_YEAR as SV_YEAR,
    PF_NAME as PREFECTURE,
    z as KIND,
    coalesce(sum(X) ,0) AMT
    from (
                  select SURVEY_YEAR, PF_CODE, ELEMENTARY as X, 1 as ty, '小学校' as  z from ENROLLMENT_STATUS where ELEMENTARY is not null
                      union all
                      select SURVEY_YEAR, PF_CODE,MIDDLE as X, 2 as ty, '中学校' as z  from ENROLLMENT_STATUS where MIDDLE is not null
                      union all
                      select SURVEY_YEAR, PF_CODE,HIGH as X, 3 as ty, '高校' as z   from ENROLLMENT_STATUS where HIGH is not null
                      union all
                      select SURVEY_YEAR, PF_CODE,JUNIOR_CLG as X, 4 as ty, '短大' as z   from ENROLLMENT_STATUS where JUNIOR_CLG is not null
                      union all
                      select SURVEY_YEAR, PF_CODE,COLLEGE as X, 5 as ty, '大学' as z   from ENROLLMENT_STATUS where COLLEGE is not null
                      union all
                      select SURVEY_YEAR, PF_CODE,GRADUATE as X, 6 as ty, '大学院' as z   from ENROLLMENT_STATUS where GRADUATE is not null
                  )
E inner join PREFECTURE P
on E.PF_CODE = P.PF_CODE
    where SURVEY_YEAR=2020
group by SURVEY_YEAR, E.PF_CODE, ty
order by E.PF_CODE, ty