with base as (
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(ELEMENTARY > 0 ,'小学校','')) AS KIND,
    MAX(IIF(ELEMENTARY > 0 ,'a','')) AS ord,
    SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(MIDDLE > 0 ,'中学校','')) AS KIND,
    MAX(IIF(MIDDLE > 0 ,'b','')) AS ord,
    SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(HIGH > 0 ,'高校','')) AS KIND,
    MAX(IIF(HIGH > 0 ,'c','')) AS ord,
    SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(JUNIOR_CLG > 0 ,'短大','')) AS KIND,
    MAX(IIF(JUNIOR_CLG > 0 ,'d','')) AS ord,
    SUM(JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(COLLEGE > 0 ,'大学','')) AS KIND,
    MAX(IIF(COLLEGE > 0 ,'e','')) AS ord,
    SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE AS PREFECTURE,
    MAX(IIF(GRADUATE > 0 ,'大学院','')) AS KIND,
    MAX(IIF(GRADUATE > 0 ,'f','')) AS ord,
    SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
order by PREFECTURE ASC, ord ASC
)
select
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
from 
    base
WHERE
    AMT >= 0