DB2 LUW多值列拆分方案咨询（无SPLIT函数支持）

针对你使用的不支持SPLIT函数的DB2 LUW 8.x/9.x/10.x/11.x版本，可以通过递归CTE实现多值列拆分，以下是具体实现方案：

实现思路 #

1. 用递归CTE分别拆分每个以ý分隔的多值列，生成每个ID对应的位置序号（pos）和该位置的元素值；
2. 将各列拆分结果按ID和位置序号关联，确保同一行的元素对应正确；
3. 处理空值（替换为0）和格式转换（小数点换逗号），最终输出目标格式。

完整SQL代码 #

假设你的表名为BALANCE_TABLE，替换为实际表名即可执行：

WITH RECURSIVE split_curr AS (
    SELECT 
        ID,
        1 AS pos,
        CASE WHEN LOCATE('ý', CURR_ASSET_TYPE) = 0 THEN CURR_ASSET_TYPE ELSE SUBSTR(CURR_ASSET_TYPE, 1, LOCATE('ý', CURR_ASSET_TYPE)-1) END AS curr_val,
        CASE WHEN LOCATE('ý', CURR_ASSET_TYPE) = 0 THEN '' ELSE SUBSTR(CURR_ASSET_TYPE, LOCATE('ý', CURR_ASSET_TYPE)+1) END AS remaining_curr
    FROM BALANCE_TABLE
    UNION ALL
    SELECT 
        ID,
        pos+1,
        CASE WHEN LOCATE('ý', remaining_curr) = 0 THEN remaining_curr ELSE SUBSTR(remaining_curr, 1, LOCATE('ý', remaining_curr)-1) END AS curr_val,
        CASE WHEN LOCATE('ý', remaining_curr) = 0 THEN '' ELSE SUBSTR(remaining_curr, LOCATE('ý', remaining_curr)+1) END AS remaining_curr
    FROM split_curr
    WHERE remaining_curr <> ''
),
split_open AS (
    SELECT 
        ID,
        1 AS pos,
        CASE WHEN LOCATE('ý', OPEN_BALANCE) = 0 THEN OPEN_BALANCE ELSE SUBSTR(OPEN_BALANCE, 1, LOCATE('ý', OPEN_BALANCE)-1) END AS open_val,
        CASE WHEN LOCATE('ý', OPEN_BALANCE) = 0 THEN '' ELSE SUBSTR(OPEN_BALANCE, LOCATE('ý', OPEN_BALANCE)+1) END AS remaining_open
    FROM BALANCE_TABLE
    UNION ALL
    SELECT 
        ID,
        pos+1,
        CASE WHEN LOCATE('ý', remaining_open) = 0 THEN remaining_open ELSE SUBSTR(remaining_open, 1, LOCATE('ý', remaining_open)-1) END AS open_val,
        CASE WHEN LOCATE('ý', remaining_open) = 0 THEN '' ELSE SUBSTR(remaining_open, LOCATE('ý', remaining_open)+1) END AS remaining_open
    FROM split_open
    WHERE remaining_open <> ''
),
split_debit AS (
    SELECT 
        ID,
        1 AS pos,
        CASE WHEN LOCATE('ý', DEBIT_MVMT) = 0 THEN DEBIT_MVMT ELSE SUBSTR(DEBIT_MVMT, 1, LOCATE('ý', DEBIT_MVMT)-1) END AS debit_val,
        CASE WHEN LOCATE('ý', DEBIT_MVMT) = 0 THEN '' ELSE SUBSTR(DEBIT_MVMT, LOCATE('ý', DEBIT_MVMT)+1) END AS remaining_debit
    FROM BALANCE_TABLE
    UNION ALL
    SELECT 
        ID,
        pos+1,
        CASE WHEN LOCATE('ý', remaining_debit) = 0 THEN remaining_debit ELSE SUBSTR(remaining_debit, 1, LOCATE('ý', remaining_debit)-1) END AS debit_val,
        CASE WHEN LOCATE('ý', remaining_debit) = 0 THEN '' ELSE SUBSTR(remaining_debit, LOCATE('ý', remaining_debit)+1) END AS remaining_debit
    FROM split_debit
    WHERE remaining_debit <> ''
),
split_credit AS (
    SELECT 
        ID,
        1 AS pos,
        CASE WHEN LOCATE('ý', CREDIT_MVMT) = 0 THEN CREDIT_MVMT ELSE SUBSTR(CREDIT_MVMT, 1, LOCATE('ý', CREDIT_MVMT)-1) END AS credit_val,
        CASE WHEN LOCATE('ý', CREDIT_MVMT) = 0 THEN '' ELSE SUBSTR(CREDIT_MVMT, LOCATE('ý', CREDIT_MVMT)+1) END AS remaining_credit
    FROM BALANCE_TABLE
    UNION ALL
    SELECT 
        ID,
        pos+1,
        CASE WHEN LOCATE('ý', remaining_credit) = 0 THEN remaining_credit ELSE SUBSTR(remaining_credit, 1, LOCATE('ý', remaining_credit)-1) END AS credit_val,
        CASE WHEN LOCATE('ý', remaining_credit) = 0 THEN '' ELSE SUBSTR(remaining_credit, LOCATE('ý', remaining_credit)+1) END AS remaining_credit
    FROM split_credit
    WHERE remaining_credit <> ''
)
SELECT 
    s.ID,
    s.curr_val AS CURR_ASSET_TYPE,
    REPLACE(COALESCE(NULLIF(TRIM(o.open_val), ''), '0'), '.', ',') AS OPEN_BALANCE,
    REPLACE(COALESCE(NULLIF(TRIM(d.debit_val), ''), '0'), '.', ',') AS DEBIT_MVMT,
    REPLACE(COALESCE(NULLIF(TRIM(c.credit_val), ''), '0'), '.', ',') AS CREDIT_MVMT
FROM split_curr s
LEFT JOIN split_open o ON s.ID = o.ID AND s.pos = o.pos
LEFT JOIN split_debit d ON s.ID = d.ID AND s.pos = d.pos
LEFT JOIN split_credit c ON s.ID = c.ID AND s.pos = c.pos
ORDER BY s.ID, s.pos;

代码说明 #

1. 递归拆分列：每个split_* CTE负责拆分对应列，通过递归逐步截取分隔符前后的内容，直到剩余字符串为空；
2. 关联结果：以split_curr（拆分资产类型列）为基准，按ID和位置序号左连接其他列的拆分结果，保证行列对应；
3. 数据处理：
   • TRIM去除元素前后空格；
   • NULLIF将空字符串转为NULL；
   • COALESCE将NULL替换为0；
   • REPLACE将小数点.替换为逗号,，匹配示例格式。

内容的提问来源于stack exchange，提问作者H. D. U.