CODEWITHSUNDEEP
sqldb2
user1613270
user1613270

Reputation: 533

DB2 SQL: Grouping records with same id in result?

if I have a table with 2 columns

text | id
---------
aaa  |  1
bbb  |  1
eee  |  1
mmm  |  2
zzz  |  2
...

Is it possible to write a query which groups by id and outputs the following result:

id | text
------------------
 1 | aaa, bbb, eee
 2 | mmm, zzz
...

Thanks!

Upvotes: 1

Views: 6794

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Use the listagg function in DB2:

select id, listagg(text, ', ') as text
from t
group by id

If you want them ordered alphabetically:

select id, listagg(text, ', ') within group (order by text) as text
from t
group by id

Upvotes: 1

John Woo
John Woo

Reputation: 263843

give this a try,

SELECT  ID, 
        SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',text))) as VARCHAR(1024)), 3)
FROM    tableName
GROUP   BY ID;

Upvotes: 1

Related Questions