Oracle SQL实现类Unpivot逻辑（无需硬编码）

针对你需要将包含Debit String、Credit String、Debit Amount、Credit Amount的表拆分为独立行的需求，以下是两种无需硬编码字段名的实现方式：

方案1：纯SQL实现（利用XML解析与元数据） #

这种方法不需要硬编码字段名，通过Oracle的XML类型和系统表元数据来动态配对借贷字段组：

WITH column_groups AS (
    -- 按Debit/Credit前缀分组，配对对应的String和Amount字段
    SELECT
        REGEXP_SUBSTR(column_name, '^(DEBIT|CREDIT)') AS dr_cr_type,
        -- 按顺序拼接同组的两个字段（String在前，Amount在后）
        LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS paired_cols
    FROM user_tab_columns
    WHERE table_name = 'YOUR_TABLE' -- 替换为你的表名（注意大写）
      AND column_name IN ('DEBIT_STRING', 'CREDIT_STRING', 'DEBIT_AMOUNT', 'CREDIT_AMOUNT')
    GROUP BY REGEXP_SUBSTR(column_name, '^(DEBIT|CREDIT)')
),
row_xml_data AS (
    -- 将每行数据转换为XML格式，方便动态提取字段值
    SELECT
        t.rowid, -- 保留原行标识，可替换为你的主键字段
        XMLTYPE(DBMS_XMLGEN.getxmltype('SELECT * FROM YOUR_TABLE WHERE ROWID = ''' || t.rowid || '''')) AS xml_row
    FROM YOUR_TABLE t
)
SELECT
    c.dr_cr_type,
    -- 提取分组中的第一个字段（String）
    EXTRACTVALUE(r.xml_row, '//ROW/' || REGEXP_SUBSTR(c.paired_cols, '[^,]+', 1, 1)) AS account_string,
    -- 提取分组中的第二个字段（Amount）
    EXTRACTVALUE(r.xml_row, '//ROW/' || REGEXP_SUBSTR(c.paired_cols, '[^,]+', 1, 2)) AS account_amount
FROM row_xml_data r
CROSS JOIN column_groups c;

说明 #

• column_groups通过系统表user_tab_columns筛选并分组借贷相关字段，避免硬编码
• row_xml_data将每行数据转为XML，实现动态字段值提取
• 最终结果会将原表的每条记录拆分为两行，分别对应借方和贷方的字符串与金额

方案2：动态SQL实现（适配字段变化） #

如果你的表可能新增其他同格式的借贷字段（比如Debit Desc、Credit Desc等），可以用动态SQL自动生成Unpivot语句：

DECLARE
    v_unpivot_sql VARCHAR2(4000);
BEGIN
    -- 动态拼接Unpivot语句，自动配对String和Amount字段
    SELECT
        'SELECT your_primary_key, dr_cr_type, account_string, account_amount FROM YOUR_TABLE ' ||
        'UNPIVOT ( (account_string, account_amount) FOR dr_cr_type IN ( ' ||
        LISTAGG(
            '(' || col_string || ', ' || REPLACE(col_string, '_STRING', '_AMOUNT') || ') AS ''' || REGEXP_SUBSTR(col_string, '^(DEBIT|CREDIT)') || '''',
            ', '
        ) WITHIN GROUP (ORDER BY col_string) ||
        ' ) )'
    INTO v_unpivot_sql
    FROM (
        SELECT column_name AS col_string
        FROM user_tab_columns
        WHERE table_name = 'YOUR_TABLE'
          AND column_name LIKE '%_STRING'
    );

    -- 执行动态SQL，可将结果插入临时表或直接输出
    EXECUTE IMMEDIATE v_unpivot_sql;
END;
/

说明 #

• 自动筛选所有以_STRING结尾的字段，并匹配对应的_AMOUNT字段
• 动态生成Unpivot语句，无需手动维护字段列表
• 注意替换your_primary_key为表的实际主键字段，用于关联原记录

注意事项 #

• Oracle表名默认区分大小写，确保YOUR_TABLE与实际表名的大小写一致（通常为大写）
• 如果不需要保留原行标识，可以删除rowid或主键字段
• 方案1适合直接作为SQL查询使用，方案2适合需要动态适配字段变更的场景

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