오라클 세로 데이터를 가로 데이터로 변경 SYS_CONNECT_BY_PATH

업무에 활용할 쿼리를 만들다 보면 여러 레코드 데이터를 한줄로 표시해야 하는 경우가 있다.
최대 레코드수를 알수있다면 쉽게 해결할테지만 불특정 다수의 레코드를 가로로 표현하는 것은 단순 쿼리로만은 어렵죠.

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(S,',')),',') AS STR
  FROM (
SELECT S,M,ROW_NUMBER() OVER (ORDER BY N) AS LN
     , COUNT (*) OVER (partition by M ) cnt
  FROM (
SELECT 10*rownum as s
     , ROWNUM N
     , mod(rownum,3) as m
    FROM
         (
          SELECT null FROM dual GROUP BY CUBE(1,2,3,4,5,6,7,8)
         )
   WHERE ROWNUM <= 30
   )
         )
 WHERE 1=1
 START WITH LN = 1
 CONNECT BY PRIOR LN = LN -1
 
 

STR
--------------------------------------------------------------------------------------------------------------
10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300

 

/** 특정그룹단위로 */
SELECT M, LTRIM(MAX(SYS_CONNECT_BY_PATH(S,',')),',') AS STR
  FROM (
SELECT S,M,ROW_NUMBER() OVER (PARTITION BY M ORDER BY N) AS LN
     , COUNT (*) OVER (partition by M ) cnt
  FROM (
SELECT 10*rownum as s
     , ROWNUM N
     , mod(rownum,3) as m
    FROM
         (
          SELECT null FROM dual GROUP BY CUBE(1,2,3,4,5,6,7,8)
         )
   WHERE ROWNUM <= 30
   )
         )
 WHERE 1=1
 START WITH LN = 1
 CONNECT BY PRIOR M=M AND PRIOR LN = LN -1
 GROUP BY M
;

M STR
--- ------------------------------------
0 30,60,90,120,150,180,210,240,270,300
1 10,40,70,100,130,160,190,220,250,280
2 20,50,80,110,140,170,200,230,260,290