group by에서 문자열 합치기...

아래내용은 구루비 커뮤니티(오라클클럽) 에서 퍼옴..   

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

;

결과는.. 다음과 같다..

TYPE    NAME_9I                 NAME_10G                NAME_11G

과일     사과,레몬,포도,참외    사과,참외,포도,레몬 사과,레몬,포도,참외

채소     오이,당근,호박    오이,호박,당근        오이,당근,호박

참고로..

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