SELECT
	SURVEY_YEAR AS SV_YEAR
	, PF_NAME AS PREFECTURE
	, CASE B.KIND_NO
			WHEN 1 THEN '小学校'
			WHEN 2 THEN '中学校'
			WHEN 3 THEN '高校'
			WHEN 4 THEN '短大'
			WHEN 5 THEN '大学'
			WHEN 6 THEN '大学院'
		END
	  AS KIND
	, SUM(
		CASE B.KIND_NO
			WHEN 1 THEN IFNULL(ELEMENTARY, 0)
			WHEN 2 THEN IFNULL(MIDDLE, 0)
			WHEN 3 THEN IFNULL(HIGH, 0)
			WHEN 4 THEN IFNULL(JUNIOR_CLG, 0)
			WHEN 5 THEN IFNULL(COLLEGE, 0)
			WHEN 6 THEN IFNULL(GRADUATE, 0)
		END
	) AS AMT
FROM
	ENROLLMENT_STATUS ES
	INNER JOIN PREFECTURE P
	ON
		ES.PF_CODE = P.PF_CODE
CROSS JOIN
  (
    SELECT
      1 AS KIND_NO
    UNION ALL
    SELECT
      2 AS KIND_NO
    UNION ALL
     SELECT
      3 AS KIND_NO
    UNION ALL
     SELECT
      4 AS KIND_NO
    UNION ALL
     SELECT
      5 AS KIND_NO
    UNION ALL
     SELECT
      6 AS inKIND_NOdex
  ) AS B
GROUP BY
  SV_YEAR
  , PREFECTURE
  , KIND_NO
ORDER BY
  P.PF_CODE
  , B.KIND_NO