WITH tmp AS(
SELECT 
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
1 AS KIND,
SUM(
CASE WHEN ELEMENTARY IS NULL THEN 0
ELSE ELEMENTARY END
)AS AMT,
SUM(
CASE WHEN ELEMENTARY IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE

UNION 

SELECT
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
2 AS KIND,
SUM(
CASE WHEN MIDDLE IS NULL THEN 0
ELSE MIDDLE END
)AS AMT,
SUM(
CASE WHEN MIDDLE IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE
UNION
SELECT
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
3 AS KIND,
SUM(
CASE WHEN HIGH IS NULL THEN 0
ELSE HIGH END
)AS AMT,
SUM(
CASE WHEN HIGH IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE
UNION
SELECT
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
4 AS KIND,
SUM(
CASE WHEN JUNIOR_CLG IS NULL THEN 0
ELSE JUNIOR_CLG END
)AS AMT,
SUM(
CASE WHEN JUNIOR_CLG IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE
UNION
SELECT
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
5 AS KIND,
SUM(
CASE WHEN COLLEGE IS NULL THEN 0
ELSE COLLEGE END
)AS AMT,
SUM(
CASE WHEN COLLEGE IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE
UNION
SELECT
SURVEY_YEAR AS SV_YEAR,
E.PF_CODE AS CODE,
6 AS KIND,
SUM(
CASE WHEN GRADUATE IS NULL THEN 0
ELSE GRADUATE END
)AS AMT,
SUM(
CASE WHEN GRADUATE IS NOT NULL THEN 1
ELSE 0 END
)AS CNT_NONNULL
FROM ENROLLMENT_STATUS E 
WHERE SV_YEAR = 2020
GROUP BY CODE
)
SELECT
t.SV_YEAR,
p.PF_NAME as PREFECTURE,
CASE
WHEN t.KIND=1 THEN "小学校"
WHEN t.KIND=2 THEN "中学校"
WHEN t.KIND=3 THEN "高校"
WHEN t.KIND=4 THEN "短大"
WHEN t.KIND=5 THEN "大学"
WHEN t.KIND=6 THEN "大学院"
END AS KIND,
t.AMT
FROM tmp t LEFT JOIN PREFECTURE p ON t.CODE = p.PF_CODE
WHERE t.CNT_NONNULL > 0 
ORDER BY t.CODE, t.KIND