[펌]Row를 한컬럼에 담아 옆으로 펼치기(SYS_CONNECT_BY_PATH)

참조 : http://tong.nate.com/lim4you/29357310

SELECT　　 SUBSTR (MAX (SYS_CONNECT_BY_PATH (emp_nm, ',')), 2) path#
　　　FROM (
              SELECT emp_nm, ROWNUM rnum
  　　　　　　FROM   thum01m
              WHERE  rownum <= 20
           )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1

결과

PATH#

--------------------------------------------

박수길,두은영,백혜성,김영미,안미경,강혜순,김선혜,황복례,서일화,조선영,한미영,김성희,이성애,김용구,이정희,이승금,서영남,이경미,문경순,김영현

Inline View와 조건은 상황에 맞게 변경해야 합니다.

 

SELECT  SUBSTR (MAX (SYS_CONNECT_BY_PATH (DT_INFO, ', ')), 3) AS DT_INFO
FROM    (
        SELECT  TA.ITEM_CODE,
                DT_INFO,
                ROW_NUMBER () OVER (PARTITION BY TA.ITEM_CODE ORDER BY UNIT_CODE) AS RANKING
        FROM    TITEM_SS TA,
                TUNIT_SS TB
        WHERE   TA.ITEM_CODE = TB.ITEM_CODE
        AND     TB.SALE_GB   = '00'
        AND     TB.UNIT_CODE > '000'
        AND     TA.ITEM_CODE = '10468636' 
        )
CONNECT BY PRIOR RANKING = RANKING - 1
START   WITH RANKING = 1 

출처 : Tong - 라제폰님의 데이타베이스통