/* Oracle ==> http://blog.naver.com/hyelee96/140073401311 */
WITH t AS
(
SELECT 'A' gbn, '001' val FROM dual UNION ALL
SELECT 'A' gbn, '002' val FROM dual UNION ALL
SELECT 'A' gbn, '003' val FROM dual UNION ALL
SELECT 'A' gbn, '004' val FROM dual UNION ALL
SELECT 'A' gbn, '005' val FROM dual UNION ALL
SELECT 'A' gbn, '011' val FROM dual UNION ALL
SELECT 'B' gbn, '007' val FROM dual UNION ALL
SELECT 'B' gbn, '004' val FROM dual UNION ALL
SELECT 'B' gbn, '005' val FROM dual UNION ALL
SELECT 'B' gbn, '010' val FROM dual UNION ALL
SELECT 'C' gbn, '' val FROM dual UNION ALL
SELECT 'C' gbn, NULL val FROM dual UNION ALL
SELECT 'C' gbn, '011' val FROM dual UNION ALL
SELECT 'C' gbn, '001' val FROM dual UNION ALL
SELECT 'C' gbn, '002' val FROM dual UNION ALL
SELECT 'C' gbn, '003' val FROM dual
)
SELECT gbn
, XMLAGG(XMLELEMENT(a,',' || val) ORDER BY val).extract('//text()').getStringVal() val1
, REGEXP_REPLACE(SUBSTR(XMLAGG(XMLELEMENT(a,',' || val) ORDER BY val).extract('//text()'), 2), '[,]+$', '') val2
FROM t
GROUP BY gbn
;
/* DB2 ==> http://www.dbforums.com/db2/1684537-aggregating-string-separated-comma-db2z-osv10-without-using-xmlagg-xmlgroup.html */
WITH t AS
(
SELECT 'A' gbn, '001' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'A' gbn, '002' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'A' gbn, '003' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'A' gbn, '004' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'A' gbn, '005' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'A' gbn, '011' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'B' gbn, '007' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'B' gbn, '004' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'B' gbn, '005' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'B' gbn, '010' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, '' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, NULL val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, '011' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, '001' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, '002' val FROM sysibm.sysdummy1 UNION ALL
SELECT 'C' gbn, '003' val FROM sysibm.sysdummy1
)
SELECT gbn
, XMLAGG(XMLELEMENT(name "x", val) order by val) val1
, XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", val) order by val) AS CLOB) val2
, REPLACE(REPLACE(REPLACE(REPLACE(
CAST(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "x", val) order by val) AS CLOB) as varchar(1000))
, '</x><x>', ',') , '<x>', '') , '</x>', ''), '<x/>', '') AS val3
FROM t
GROUP BY gbn
;