db2中的Listagg替代 [英] Listagg alternative in db2
问题描述
我的客户正在使用没有listagg功能的db2数据库,但我需要以某种方式聚合一个字段中的主键信息。
My customer is using db2 database without listagg function, but I need to somehow aggregate the primary key information within one field.
现在(对于Oracle)我是使用它作为更大的查询的一部分:
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
有一种替代方法来获取db2数据库中的dbarch数据库的结果,在DB2之前从版本9.7修复包4 1 ?
It there an alternative way to get result of listagg function in db2 database before DB2 as of version 9.7 Fix Pack 41 ?
我的客户数据库的版本:
Linux - 企业版服务器版本9.7,版本号08060107
我通过执行这些选项得到它: p>
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;
我承认我不明白,怎么可能是9.7,但是没有listagg功能? ! :confused:
I admit I don't understand, how can it be 9.7, but there is not listagg function?! :confused:
我也执行了:
SELECT * FROM SYSCAT.FUNCTIONS
我收到了这个功能列表,但是没有像xmltext或xmlgroup这样的功能在替代解决方案中提到的答案:(。$ b $客户使用的是什么neanderthal数据库?还是我缺少某些东西?
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?
感谢您的回复。
推荐答案
如果您的DB2版本支持pureXML(至少DB2 for LUW 9.1,我相信DB2 9 for z / OS),除了上面提到的@ PM77-1之外可以使用XMLAGG函数:
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
...
这篇关于db2中的Listagg替代的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!