DB2 SQL实现同一儿童多条玩具记录合并为单行的需求及报错解决求助

没问题，我来帮你搞定这个问题！先理清楚你的场景和遇到的问题：

你的原始数据表 #

你有一个DB2表KIDS_TOYS，数据如下：

需求目标 #

你希望将每个孩子的多条玩具记录合并为单行，最终输出结果如下：

你遇到的问题 #

你尝试了一段XML聚合的SQL，但触发了DB2 SQL16003N错误：

DB2 SQL16003N an expression of data type "( item(), item() + )" cannot be used when the data type "item()" is expected in the context

你的原始SQL代码：

SELECT 
    KID_ID,
    F_NAME,
    L_NAME,
    RTRIM(
        XMLCAST(
            XMLAGG(
                XMLELEMENT(NAME X,
                    TOY_NAME || ':' || HAS_IT || ' '
                )
                ORDER BY TOY_NAME
            ) AS VARCHAR(2000)
        ), ' ')  AS TOYS_LIST
FROM 
    KIDS_TOYS
GROUP BY 
    KID_ID, F_NAME, L_NAME
ORDER BY 
    KID_ID;

解决方案 #

这个错误是因为XMLELEMENT生成的带标签XML元素在聚合后，类型转换时出现了不兼容的问题。我给你两种可行的解决方法：

方法1：使用LISTAGG函数（推荐，DB2 11.1及以上版本支持）

LISTAGG是DB2专门为字符串聚合场景设计的函数，用法更简洁高效：

SELECT 
    KID_ID,
    F_NAME,
    L_NAME,
    LISTAGG(TOY_NAME || ':' || HAS_IT, ' ') 
        WITHIN GROUP (ORDER BY TOY_NAME) AS TOYS
FROM 
    KIDS_TOYS
GROUP BY 
    KID_ID, F_NAME, L_NAME
ORDER BY 
    KID_ID;

• 说明：LISTAGG(要连接的字符串, 分隔符)会自动将分组内的字符串按指定分隔符连接，WITHIN GROUP (ORDER BY TOY_NAME)保证玩具按你需要的顺序排列，完全符合你的输出要求。

方法2：调整XML聚合的写法（兼容低版本DB2）

如果你的DB2版本不支持LISTAGG，可以修改XML处理的逻辑，用XMLTEXT替代XMLELEMENT来处理纯文本：

SELECT 
    KID_ID,
    F_NAME,
    L_NAME,
    RTRIM(
        XMLCAST(
            XMLAGG(
                XMLTEXT(TOY_NAME || ':' || HAS_IT || ' ')
                ORDER BY TOY_NAME
            ) AS VARCHAR(2000)
        ), ' ') AS TOYS
FROM 
    KIDS_TOYS
GROUP BY 
    KID_ID, F_NAME, L_NAME
ORDER BY 
    KID_ID;

• 说明：XMLTEXT直接将字符串作为纯文本节点，避免了XMLELEMENT生成的XML标签带来的类型转换问题，之后的RTRIM用来去掉最后一个多余的空格。

这两种方法都能得到你期望的单行结果，根据你的DB2版本选择即可！

备注：内容来源于stack exchange，提问作者Jason Miles