DB2中多子串列转行替代UNION ALL的技术方案问询

需求：将Product字段中的多个子串堆叠到同一列（而非生成多列），由于实际数据量庞大且每行存在30余个子串，希望避免使用UNION ALL。现有SQL可解析子串但会生成多列，尝试过INNER JOIN和UNPIVOT未成功，使用DB2数据库，期望得到指定格式的结果。

现有SQL代码 #

SELECT Prod_Typ, 
    SUBSTR(Product, 1, 3) as Smp,
    SUBSTR(Product, 4, 4) as Smp_Num,
    SUBSTR(Product, 8, 3) as Smp1,
    SUBSTR(Product, 11, 4) as Smp_Num1,
    SUBSTR(Product, 15, 3) as Smp2,
    SUBSTR(Product, 18, 4) as Smp_Num2,
    SUBSTR(Product, 22, 3) as Smp3,
    SUBSTR(Product, 25, 4) as Smp_Num3
FROM my_table

原始数据 #

期望结果 #

解决方案 #

针对DB2数据库，推荐两种无需大量UNION ALL的实现方式，适配30余个子串的场景：

方案1：递归CTE动态遍历子串 #

通过递归公共表表达式（CTE）自动遍历每个子串的起始位置，无需手动编写重复逻辑：

WITH RECURSIVE sub串位置 AS (
    -- 初始行：提取第一个子串
    SELECT 
        Prod_Typ,
        Product,
        1 AS smp_start,
        SUBSTR(Product, 1, 3) AS Smp,
        -- 仅当后续4位为有效数字时提取Smp_Num，否则为空
        CASE 
            WHEN LTRIM(SUBSTR(Product, 4, 4), ' ') <> '' 
                 AND SUBSTR(Product, 4, 4) NOT LIKE '%[^0-9]%' 
            THEN SUBSTR(Product, 4, 4) 
            ELSE '' 
        END AS Smp_Num
    FROM my_table
    WHERE SUBSTR(Product, 1, 3) <> ''
    UNION ALL
    -- 递归提取后续子串
    SELECT 
        st.Prod_Typ,
        st.Product,
        st.smp_start + 7 AS smp_start,
        CASE 
            WHEN st.smp_start +7 > LENGTH(st.Product) THEN ''
            ELSE SUBSTR(st.Product, st.smp_start +7, 3) 
        END AS Smp,
        CASE 
            WHEN st.smp_start +10 > LENGTH(st.Product) THEN ''
            WHEN LTRIM(SUBSTR(st.Product, st.smp_start +10, 4), ' ') <> '' 
                 AND SUBSTR(st.Product, st.smp_start +10, 4) NOT LIKE '%[^0-9]%'
            THEN SUBSTR(st.Product, st.smp_start +10, 4)
            ELSE ''
        END AS Smp_Num
    FROM sub串位置 st
    WHERE st.smp_start +7 <= LENGTH(st.Product)
)
SELECT Prod_Typ, TRIM(Smp) AS Smp, TRIM(Smp_Num) AS Smp_Num
FROM sub串位置
WHERE TRIM(Smp) <> ''
ORDER BY Prod_Typ, smp_start;

方案2：数字生成表交叉连接 #

先生成包含1到30的数字表，通过交叉连接批量提取每个位置的子串：

-- 生成1到30的数字表，对应30个子串位置
WITH 数字表 AS (
    SELECT ROW_NUMBER() OVER() AS num
    FROM sysibm.sysdummy1
    CONNECT BY LEVEL <= 30
)
SELECT 
    t.Prod_Typ,
    TRIM(SUBSTR(t.Product, (n.num-1)*7 +1, 3)) AS Smp,
    TRIM(CASE 
        WHEN SUBSTR(t.Product, (n.num-1)*7 +4, 4) NOT LIKE '%[^0-9]%' 
        THEN SUBSTR(t.Product, (n.num-1)*7 +4, 4) 
        ELSE '' 
    END) AS Smp_Num
FROM my_table t
CROSS JOIN 数字表 n
WHERE TRIM(SUBSTR(t.Product, (n.num-1)*7 +1, 3)) <> ''
ORDER BY t.Prod_Typ, n.num;

说明：

• 两种方案均避免了手动编写30余条UNION ALL语句，提升代码可维护性
• 若子串间隔不是固定的7位（3位Smp+4位Smp_Num），可根据实际格式调整起始位置的计算逻辑
• 自动过滤空的Smp行，确保结果符合预期

内容的提问来源于stack exchange，提问作者Twinkks