Group By 에서 문자열 합치기 - 버전별 정리

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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 ;

10g에서 수정

1 2 3 4

WITH t AS ( SELECT '과일' AS type, '사과' AS name, '0' AS code FROM dual -- 이하 마농님 코드와 동일합니다.

1 2 3 4 5 6 7

-- MySQL -- SELECT type      , group_concat(name ORDER BY code) name_MySQL   FROM t  GROUP BY type  ORDER BY type ;

?

1 2 3 4 5 6 7 8 9 10 11 12

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

[출처] http://oracleclub.com/article/55512