-- 8i --
SELECT num
, MIN(DECODE(rn,1,''''||data||''''))
|| MIN(DECODE(rn,2,','''||data||''''))
|| MIN(DECODE(rn,3,','''||data||''''))
FROM (SELECT num, data
, ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn
FROM t
)
GROUP BY num
;
-- 9i --
SELECT num
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(''''||data||'''',',')),2) data
FROM (SELECT num, data
, ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn
FROM t
)
START WITH rn = 1
CONNECT BY PRIOR num = num
AND PRIOR rn = rn - 1
GROUP BY num
;
-- 10G --
SELECT num
, REPLACE(SUBSTR(data,2),'@','''') data
FROM
(
SELECT num,
XMLAGG(XMLELEMENT(x,',@'||data||'@') ORDER BY data).EXTRACT('//text()').GetStringVal() data
FROM t
GROUP BY num
)
;
10G XML 함수에서는 따옴표가 ' 로 표시되는 관계로
부득이하게 @ 로 대체해서 다시한번 처리했습니다.