sql pivot row to column (행을 열로) - 구구단예제

응용에 필요한 기초라 작성해 둔다. 나이가 들수록 순간적으로 기억이 나지 않는다.ㅠㅠ

 

1. 첫번째

   WITH T1 AS
   (
       SELECT LEVEL NO FROM DUAL CONNECT BY LEVEL <=9
   )
   SELECT
       SUBSTR(RN,2,1) AS NO,
       MIN(CASE WHEN RN <= 19 THEN MT END) AS "1단",
       MIN(CASE WHEN RN BETWEEN 21 AND 29 THEN MT END) AS "2단",
       MIN(CASE WHEN RN BETWEEN 31 AND 39 THEN MT END) AS "3단",
       MIN(CASE WHEN RN BETWEEN 41 AND 49 THEN MT END) AS "4단",
       MIN(CASE WHEN RN BETWEEN 51 AND 59 THEN MT END) AS "5단",
       MIN(CASE WHEN RN BETWEEN 61 AND 69 THEN MT END) AS "6단",
       MIN(CASE WHEN RN BETWEEN 71 AND 79 THEN MT END) AS "7단",
       MIN(CASE WHEN RN BETWEEN 81 AND 89 THEN MT END) AS "8단",
       MIN(CASE WHEN RN BETWEEN 91 AND 99 THEN MT END) AS "9단"
   FROM
   (
       SELECT T1.NO||T2.NO RN,T1.NO||'*'||T2.NO||'='||LPAD(T1.NO*T2.NO,2,' ') AS MT FROM T1,T1 T2
   ) 
   GROUP BY SUBSTR(RN,2,1)
   ORDER BY SUBSTR(RN,2,1)

2. 두번째 : 11G부터는 PIVOT함수가 지원됩니다.

   WITH T1 AS
   (
       SELECT LEVEL NO FROM DUAL CONNECT BY LEVEL <=9
   )
   SELECT
   *
   FROM
   (
       SELECT
           SUBSTR(RN,2,1) AS NO,
           (
           CASE 
            WHEN RN <= 19 THEN '1단' 
            WHEN RN BETWEEN 21 AND 29 THEN '2단'
            WHEN RN BETWEEN 31 AND 39 THEN '3단'
            WHEN RN BETWEEN 41 AND 49 THEN '4단'
            WHEN RN BETWEEN 51 AND 59 THEN '5단'
            WHEN RN BETWEEN 61 AND 69 THEN '6단'
            WHEN RN BETWEEN 71 AND 79 THEN '7단'
            WHEN RN BETWEEN 81 AND 89 THEN '8단'
            ELSE '9단' END ) AS DAN,
           MT
       FROM
       (
           SELECT T1.NO||T2.NO RN,T1.NO||'*'||T2.NO||'='||LPAD(T1.NO*T2.NO,2,' ') AS MT FROM T1,T1 T2
       ) 
   )
   PIVOT 
   (
       MIN(MT) FOR DAN IN ('1단','2단','3단','4단','5단','6단','7단','8단','9단')
   )
   ORDER BY NO