with vTbl as
        (select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'小学校' KIND
            ,1 as rnk 
            ,sum(ELEMENTARY) AMT
            from ENROLLMENT_STATUS es
            where ELEMENTARY>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        union all
            select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'中学校' KIND
            ,2 as rnk 
            ,sum(MIDDLE) AMT
            from ENROLLMENT_STATUS es
            where MIDDLE>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        union all
            select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'高校' KIND
            ,3 as rnk 
            ,sum(HIGH) AMT
            from ENROLLMENT_STATUS es
            where HIGH>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        union all
            select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'短大' KIND
            ,4 as rnk 
            ,sum(JUNIOR_CLG) AMT
            from ENROLLMENT_STATUS es
            where JUNIOR_CLG>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        union all
            select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'大学' KIND
            ,5 as rnk 
            ,sum(COLLEGE) AMT
            from ENROLLMENT_STATUS es
            where COLLEGE>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        union all
            select
             es.SURVEY_YEAR 
            ,PF_CODE
            ,'大学院' KIND
            ,6 as rnk 
            ,sum(GRADUATE) AMT
            from ENROLLMENT_STATUS es
            where GRADUATE>0
            and es.SURVEY_YEAR=2020
            group by
             PF_CODE
        )
        select 
         t.SURVEY_YEAR as SV_YEAR
        ,p.PF_NAME as PREFECTURE
        ,t.KIND as KIND
        ,t.AMT as AMT
        from vTbl t
        inner join PREFECTURE p
            on t.PF_CODE = p.PF_CODE
        order by 
          t.PF_CODE asc
         ,t.rnk