Oracle Database SQL：将列数据拆分至多列多行并存储到新表

原表结构 #

-- 假设原表名为 source_table
ID        Value                      Timestamp
1    {A,B,C,D}{E,F,G,H}{I,J,K,L}     12-12-2022

目标表结构 #

-- 假设目标表名为 target_table
ID    Value1   Value2     Value3    Value4   Timestamp
1       A        B           C        D      12-12-2022
1       E        F           G        H      12-12-2022
1       I        J           K        L      12-12-2022

实现SQL语句 #

WITH split_groups AS (
    -- 拆分Value字段中的每个{}分组
    SELECT 
        id,
        TRIM(BOTH '{}' FROM REGEXP_SUBSTR(value, '\{[^}]+\}', 1, level)) AS group_value,
        timestamp
    FROM source_table
    CONNECT BY 
        level <= REGEXP_COUNT(value, '\{[^}]+\}')
        AND PRIOR id = id
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免递归产生重复数据
),
split_values AS (
    -- 将每个分组拆分为单个值，映射到对应列
    SELECT 
        id,
        timestamp,
        REGEXP_SUBSTR(group_value, '[^,]+', 1, 1) AS value1,
        REGEXP_SUBSTR(group_value, '[^,]+', 1, 2) AS value2,
        REGEXP_SUBSTR(group_value, '[^,]+', 1, 3) AS value3,
        REGEXP_SUBSTR(group_value, '[^,]+', 1, 4) AS value4
    FROM split_groups
)
-- 将结果插入目标表，若只需查询结果可直接执行SELECT语句
INSERT INTO target_table (id, value1, value2, value3, value4, timestamp)
SELECT id, value1, value2, value3, value4, timestamp
FROM split_values;

关键逻辑说明 #

• 拆分分组：利用REGEXP_SUBSTR匹配{...}格式的分组，结合CONNECT BY递归遍历所有分组，再通过TRIM去除分组首尾的{}符号。
• 拆分单个值：再次使用REGEXP_SUBSTR按逗号分隔每个分组内的四个值，分别映射到目标表的Value1至Value4列。
• 递归防重复：PRIOR SYS_GUID() IS NOT NULL用于避免同一行数据在递归过程中生成重复结果。

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