SQL concatenate rows into one field (DB2)

Question

I need to extract some data from my DB2 database. I have keywords in one table and each keyword is a separate row. In the extract I want to have all keywords for all IDs in one string separated by a comma.

So, to explain: I have such data

ID         | keyword 
===========================
prd1       | test
---------------------------
prd2       | keywords
---------------------------
prd1       | flower
---------------------------
prd4       | picture
---------------------------
prd7       | 234567
---------------------------
prd9       | reored
---------------------------
prd4       | finland
---------------------------
prd0       | 983y23
---------------------------
prd4       | code
---------------------------
prd9       | tratata

And here is a sample of what I want to achieve:

ID         | keyword concatenated
===========================
prd1       | test, flower
---------------------------
prd2       | keywords
---------------------------
prd4       | picture, finland, code
---------------------------
prd7       | 234567
---------------------------
prd9       | reored, tratata
---------------------------
prd0       | 983y23
---------------------------

I tried with concat:

SELECT concat(keyword) FROM table.keywordTbl

I tried with some DB2 specific commands:

SELECT IDs, Sys_Connect_By_Path(varchar(keyword), ', ') AS "keyword concatenated" FROM table.keywordTbl START WITH ID='prd1' CONNECT BY ID=keyword

PIVOT is not right for this, but I have checked it too. GROUP_CONCAT is not working either.

Of course, none of these work...

Do you have any hints for me?

score 33 · Answer 1 · edited Feb 20 '19 at 23:51

33

You can also use LISTAGG() function, if you have DB2 v9.7+. The first parameter is the column, the second is separator.

SELECT 
  ID
, LISTAGG(keyword, ',') AS "keyword concatenated"
    FROM table.keywordTbl 
GROUP BY ID

edited Feb 20 '19 at 23:51

SovietFrontier

2,047
1
15
33

answered Jun 29 '17 at 08:57

zlidime

1,124
11
6

1

This works amazingly! Adding to this if one wants to concatenate removing duplicates one can pass in Distinct to ListAgg. ,LISTAGG(Distinct keyword, ',') as "keyword concatenated" – Josh Pachner Jan 26 '21 at 16:25

score 9 · Accepted Answer · edited Jun 28 '17 at 16:50

9

Try to modify query accordingly to the below script

 SELECT 
    ID, 
    SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',keyword))) as VARCHAR(1024)), 3) AS "keyword concatenated"
    FROM table.keywordTbl 
    GROUP BY ID

edited Jun 28 '17 at 16:50

Esperento57

16,521
3
39
45

answered Jun 28 '17 at 14:23

mohan111

8,633
4
28
55

SQL concatenate rows into one field (DB2)

2 Answers2

Linked