CODEWITHSUNDEEP
sqlsybase
Alpesh003
Alpesh003

Reputation: 329

combine multiple records into one cell in sql db2

I have a table with two columns id and comments:

    id comment
    1   test1
    1   test2
    1   test3
    2   test4
    3   test5

How can we achieve the below using sql db2 query: id comment

    1   test1,test2,test3
    2   test4
    3   test5

Please help.

Upvotes: 1

Views: 1344

Answers (1)

Linger
Linger

Reputation: 15048

You can use LISTAGG:

SELECT id, LISTAGG(comment, ',') as comments 
FROM myTable 
GROUP BY id

Or you could try:

SELECT id,
SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', comment))) as VARCHAR(1024)), 3)
FROM myTable
GROUP BY id

Or try:

SELECT id, GROUP_CONCAT(comment SEPARATOR ", ") as comments 
FROM myTable 
GROUP BY id

Or:

SELECT CAT.id,
       STUFF((SELECT ',' + SUB.comment AS [text()]
              FROM MyTable SUB
              WHERE SUB.id = CAT.id
              FOR XML PATH('')
             ), 1, 1, '' ) AS Comments
FROM MyTable CAT

Upvotes: 1

Related Questions