집계 쿼리 행열 변환 (가로 출력)

1. 행열변환

SELECT SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
       , SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
       , SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
       , SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
       , SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
       , SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
       , SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
       , SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
       , SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
       , SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
       , SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
       , SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
  FROM (SELECT MONTH(HIRE_DATE) AS MM
               , COUNT(*) AS CNT
       	  FROM EMPLOYEES 
	     WHERE YEAR(HIRE_DATE)=1999 
	     GROUP BY MONTH(HIRE_DATE)
	   ) DS

 

2. 행열변환 응용1

SELECT YR
       , SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
       , SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
       , SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
       , SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
       , SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
       , SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
       , SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
       , SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
       , SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
       , SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
       , SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
       , SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
  FROM (SELECT YEAR(HIRE_DATE) AS YR
               , MONTH(HIRE_DATE) AS MM
               , COUNT(*) AS CNT
       	  FROM EMPLOYEES 
	     WHERE YEAR(HIRE_DATE) IN ('1998', '1999') 
	     GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
	   ) DS
 GROUP BY YR