wm_concat 써보세요

WITH T AS (
    SELECT 1 NO, '사과' NM FROM DUAL UNION ALL
    SELECT 1   , '밤'      FROM DUAL UNION ALL
    SELECT 2   , '배'      FROM DUAL UNION ALL
    SELECT 2   , '참외'    FROM DUAL UNION ALL
    SELECT 2   , '복숭아'  FROM DUAL
)
SELECT T.NO,
       SUBSTR(XMLAGG(XMLELEMENT(A, ',', T.NM)).EXTRACT('//text()'), 2) NM_9i,
       TO_CHAR(WM_CONCAT(T.NM)) NM_10g,
       LISTAGG(T.NM, ',') WITHIN GROUP (ORDER BY T.NM) NM_11g
FROM   T
GROUP BY T.NO

http://www.gurubee.net/article/55512

감사합니다