|
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 ;
|
