WITH t(type, name, code) AS
(
SELECT '과일', '사과', '0' FROM dual
UNION ALL SELECT '과일', '레몬', '1' FROM dual
UNION ALL SELECT '과일', '포도', '2' FROM dual
UNION ALL SELECT '과일', '참외', '3' FROM dual
UNION ALL SELECT '채소', '오이', '0' FROM dual
UNION ALL SELECT '채소', '당근', '1' FROM dual
UNION ALL SELECT '채소', '호박', '2' FROM dual
)
SELECT type
, SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()'), 2) name_9i
, wm_concat(name) name_10g
, ListAgg(name, ',') WITHIN GROUP(ORDER BY code) name_11g
FROM t
GROUP BY type
ORDER BY type
;
TYPE NAME_9I NAME_10G NAME_11G
---- ------------------------- ------------------------- -------------------------
과일 사과,레몬,포도,참외 사과,참외,포도,레몬 사과,레몬,포도,참외
채소 오이,당근,호박 오이,호박,당근 오이,당근,호박
-- 오류 : ORA-01489: 문자열 연결의 결과가 너무 깁니다.
-- 원인 : 4000 Byte 이상 문자 연결.
-- 해결 : XMLAgg 의 getCLOBval() 기능 이용.
SELECT type
, SUBSTR(
XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()').getCLOBval()
, 2) name_clob
FROM t
GROUP BY type
ORDER BY type
;
출처 : http://www.oracleclub.com/article/55512
최신 댓글