--survey_YEAR->SV_year=2020
--PF_NAME->PREFECTURE
--都道府県、就学先ごとで集計
--都道府県でGroupby->ElementaryのsumをAMTに入れる？


with base as (
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    MAX(IIF(ELEMENTARY > 0 ,'小学校','')) AS KIND,
    MAX(IIF(ELEMENTARY > 0 ,'a','')) AS ord,
    SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020 and ELEMENTARY is not null
GROUP BY
    PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    MAX(IIF(MIDDLE > 0 ,'中学校','')) AS KIND,
    MAX(IIF(MIDDLE > 0 ,'b','')) AS ord,
    SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and MIDDLE is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    MAX(IIF(HIGH > 0 ,'高校','')) AS KIND,
    MAX(IIF(HIGH > 0 ,'c','')) AS ord,
    SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and HIGH is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    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 and JUNIOR_CLG is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    MAX(IIF(COLLEGE > 0 ,'大学','')) AS KIND,
    MAX(IIF(COLLEGE > 0 ,'e','')) AS ord,
    SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and COLLEGE is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    MAX(IIF(GRADUATE > 0 ,'大学院','')) AS KIND,
    MAX(IIF(GRADUATE > 0 ,'f','')) AS ord,
    SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and GRADUATE is not null
    GROUP BY
        PF_CODE
order by PF_CODE ASC, ord ASC
)
select
    SV_YEAR,
    PF_NAME AS PREFECTURE,
    KIND,
    AMT
from 
    base
left join
    PREFECTURE p
on  base.PF_CODE = p.PF_CODE