아래내용은 구루비 커뮤니티(오라클클럽) 에서 퍼옴..
http://www.gurubee.net/article/55512
오라클 버전별로 버전이다..
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
;
결과는.. 다음과 같다..
참고로..
MySql
SELECT type
, group_concat(name ORDER BY code) name_MySQL
FROM t
GROUP BY type
ORDER BY type
MsSQL
SELECT type
, STUFF((SELECT ',' + name
FROM t
WHERE type = a.type
ORDER BY code
FOR XML PATH('')
), 1, 1, '') name_MSSQL
FROM t a
GROUP BY type
ORDER BY type