WITH t(type, name,code) AS
(
SELECT 'A', '사과', '0' FROM dual
UNION ALL SELECT 'A', '레몬', '1' FROM dual
UNION ALL SELECT 'A', '포도', '2' FROM dual
UNION ALL SELECT 'A', '참외', '3' FROM dual
)
SELECT type
     , SUBSTR(XMLAgg(XMLElement(x, ',', name) ORDER BY code).Extract('//text()'), 2) name_9i
     , SUBSTR(XMLAgg(XMLElement(x, ',', code) ORDER BY code).Extract('//text()'), 2) code_9i
     , wm_concat(name) name_10g
     , wm_concat(code) code_10g
     , ListAgg(name, ',') WITHIN GROUP(ORDER BY code) name_11g
     , ListAgg(code, ',') WITHIN GROUP(ORDER BY code) code_11g
  FROM t
 GROUP BY type
;