SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '小学校' as 'KIND',
  0 as 'NO',
 ELEMENTARY as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(ELEMENTARY) AS 'ELEMENTARY'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
UNION ALL
SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '中学校' as 'KIND',
   1 as 'NO',
 MIDDLE as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(MIDDLE) AS 'MIDDLE'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
UNION ALL
SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '高校' as 'KIND',
   2 as 'NO',
 HIGH as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(HIGH) as 'HIGH'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
UNION ALL
SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '短大' as 'KIND',
   3 as 'NO',
 JUNIOR_CLG as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(JUNIOR_CLG) as 'JUNIOR_CLG'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
UNION ALL
SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '大学' as 'KIND',
   4 as 'NO',
 COLLEGE as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(COLLEGE) as 'COLLEGE'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
UNION ALL
SELECT 
 SURVEY_YEAR as 'SV_YEAR',
 PF_NAME as 'PREFECTURE',
  '大学院' as 'KIND',
   5 as 'NO',
 GRADUATE as 'AMT'
 FROM
(SELECT
    SURVEY_YEAR,
    PF_NAME,
    SUM(GRADUATE) as 'GRADUATE'
FROM
    ENROLLMENT_STATUS ES
    LEFT JOIN 
        PREFECTURE PRF
    ON ES.PF_CODE = PRF.PF_CODE
WHERE
SURVEY_YEAR = 2020
GROUP BY
SURVEY_YEAR,
    PF_NAME
) BASE
WHERE AMT is not null
order by PREFECTURE,NO