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 9.7 Fix Pack 4 之前，有另一种方法可以在 db2 数据库中获取 listagg 函数的结果¹ ?

It there an alternative way to get result of listagg function in db2 database before DB2 as of version 9.7 Fix Pack 4¹ ?

我的客户数据库的版本:Linux - 企业服务器版本 9.7，发布号 08060107
我通过执行这些选择得到了它:

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功能?！:困惑:

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 之类的功能:(.客户使用的是什么尼安德特人数据库?还是我错过了什么?

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屋！