오라클 버전별로 버전이다..
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
과일 사과,레몬,포도,참외 사과,참외,포도,레몬 사과,레몬,포도,참외
채소 오이,당근,호박 오이,호박,당근 오이,당근,호박
참고로..
SELECT type
, group_concat(name ORDER BY code) name_MySQL
FROM t
GROUP BY type
ORDER BY type
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
