Listagg alternative in db2
If your version of DB2 supports pureXML (that would be at least DB2 for LUW 9.1 and I believe DB2 9 for z/OS), in addition to what @PM77-1 suggested above, you could use the XMLAGG function:
select xmlserialize(
xmlagg(
xmlconcat(
xmltext(column_name),
xmltext(':'),
xmltext(content),
xmltext(',')
)
) as varchar(10000)
)
from
yourtable
...
Related videos on Youtube
Comments
-
Ondřej Vykouk almost 2 years
My customer is using db2 database without listagg function, but I need to somehow aggregate the primary key information within one field.
Right now (for Oracle) I am using this as a part of bigger query:
SELECT LISTAGG(COLUMN_NAME || ':' || CONTENT, ',') WITHIN GROUP (ORDER BY COLUMN_NAME || ':' || CONTENT) FROM TABLE WHERE ROW_IDENTIFIER_ID = I.REC_ID AND I.TABLE_RESULT_ID = T.REC_ID
It there an alternative way to get result of listagg function in db2 database before DB2 as of version 9.7 Fix Pack 41 ?
Version of my customer's database: Linux - Enterprise server edition 9.7, release number 08060107
I got it by executing these selects:SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO()) AS SYSTEMINFO; SELECT * FROM TABLE(SYSPROC.ENV_GET_PROD_INFO()) AS SYSTEMINFO; SELECT * FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO;
I admit I don't understand, how can it be 9.7, but there is not listagg function?! :confused:
I also did executed:
SELECT * FROM SYSCAT.FUNCTIONS
I got back this function list, but there are no functions like xmltext or xmlgroup mentioned in alternative solutions down in the answers:(. What neanderthal database is the customer using? Or am I missing something?
Thanks for the responses.
-
PM 77-1 over 10 yearsSee if this workaround fits your needs.
-
WarrenT over 10 yearsWhich DB2 server are you using: DB2 for i, LUW, or z/OS? And which version? Different DB2 platforms have different sets of built-in functions.
-
Ondřej Vykouk over 10 yearsUpdated question with version info and function list.
-
Clockwork-Muse over 10 yearsI believe the XML extensions are optional, so they may not be installed. It's actually possible to do this with a recursive CTE (I've done this on an AS/400); unfortunately, I no longer have access to an instance to work against.
-
-
Ondřej Vykouk over 10 yearsPureXML support is surprisingly available, but functions are not listed in the select mentioned in the question. Together with workaround tip from PM 77-1 I managed to find a solution. Many thanks.
-
mathiasrw over 7 years"I managed to find a solution" Would be awesome if you could share the solution you found...