SELECT
    MAIN.*
FROM (
SELECT
  ES.SURVEY_YEAR AS SV_YEAR,
  PREF.PF_NAME AS PREFECTURE,
  CASE WHEN CONV.IDX = 1 THEN '小学校'
       WHEN CONV.IDX = 2 THEN '中学校'
       WHEN CONV.IDX = 3 THEN '高校'
       WHEN CONV.IDX = 4 THEN '短大'
       WHEN CONV.IDX = 5 THEN '大学'
       ELSE '大学院'
  END AS KIND,
  CASE WHEN CONV.IDX = 1 THEN ES.ELEMENTARY
       WHEN CONV.IDX = 2 THEN ES.MIDDLE
       WHEN CONV.IDX = 3 THEN ES.HIGH
       WHEN CONV.IDX = 4 THEN ES.JUNIOR_CLG
       WHEN CONV.IDX = 5 THEN ES.COLLEGE
       ELSE ES.GRADUATE
  END AS AMT
FROM ENROLLMENT_STATUS AS ES
INNER JOIN PREFECTURE AS PREF
ON PREF.PF_CODE = ES.PF_CODE
CROSS JOIN (
  SELECT 1 AS IDX
  UNION ALL
  SELECT 2 AS IDX
  UNION ALL
  SELECT 3 AS IDX
  UNION ALL
  SELECT 4 AS IDX
  UNION ALL
  SELECT 5 AS IDX
  UNION ALL
  SELECT 6 AS IDX
) AS CONV
WHERE ES.SURVEY_YEAR = 2020
) AS MAIN
WHERE MAIN.AMT IS NOT NULL
ORDER BY MAIN.PREFECTURE DESC,
  CASE WHEN MAIN.KIND = '小学校' THEN 1
       WHEN MAIN.KIND = '中学校' THEN 2
       WHEN MAIN.KIND = '高校' THEN 3
       WHEN MAIN.KIND = '短大' THEN 4
       WHEN MAIN.KIND = '大学' THEN 5
       ELSE 6
  END

;