SQL) 세로를 가로의 데이터로 만들기

1) 세로를 가로의 데이터로 만들기

/* Formatted on 2010/10/07 14:11 (Formatter Plus v4.8.8) */
WITH tmp AS
     (SELECT '1' AS col1, 'A' AS col2
        FROM DUAL
      UNION ALL
      SELECT '2' AS col1, '가' AS col2
        FROM DUAL
      UNION ALL
      SELECT '1' AS col1, 'B' AS col2
        FROM DUAL
      UNION ALL
      SELECT '1' AS col1, 'C' AS col2
        FROM DUAL)
SELECT     col1, LTRIM (SYS_CONNECT_BY_PATH (col2, ','), ',') AS col1
      FROM (SELECT col1, col2,
                   ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col1) rn,
                   COUNT (*) OVER (PARTITION BY col1) cnt
              FROM tmp)
     WHERE LEVEL = cnt
START WITH rn = 1
CONNECT BY PRIOR col1 = col1 AND PRIOR rn = rn - 1;


2) 세로를 가로의 컬럼 데이터로 만들기
/* Formatted on 2010/10/07 16:07 (Formatter Plus v4.8.8) */
WITH tmp AS
     (SELECT '1' AS col1, 'a' AS col2
        FROM DUAL
      UNION ALL
      SELECT '2' AS col1, '가' AS col2
        FROM DUAL
      UNION ALL
      SELECT '1' AS col1, 'b' AS col2
        FROM DUAL
      UNION ALL
      SELECT '1' AS col1, 'c' AS col2
        FROM DUAL)
SELECT   col1, MIN (DECODE (r, 1, col2)), MIN (DECODE (r, 2, col2)),
         MIN (DECODE (r, 2, col2))
    FROM (SELECT col1, col2,
                 ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col2) r
            FROM tmp)
GROUP BY col1