Menu
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?
625
You can concatenate strings by using the CONCAT operator or the CONCAT built-in function. When the operands of two strings are concatenated, the result of the expression is a string. The operands of concatenation must be compatible strings.
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
