根据2列填充列

Oracle安装程序 ：

CREATE TABLE your_table (  name  VARCHAr2(7)        CHECK ( name IS NULL OR REGEXP_LIKE( name, '^[A-D](+[A-D]){0,3}$', 'i' ),  value VARCHAr2(200),  A     VARCHAr2(50),  B     VARCHAr2(50),  C     VARCHAr2(50),  D     VARCHAr2(50));INSERT INTO your_table ( name, value, a, b, c, d )  SELECt 'a+b+c+d', 'x2+x1+x3+x5', NULL, NULL, NULL, NULL FROM DUAL UNIOn ALL  SELECt 'a+d+c',   'y7+y3+y4',    NULL, NULL, NULL, NULL FROM DUAL;

更新声明 ：

MERGE INTO your_table dstUSING (  WITH splitstrings ( rid, name, value, col, val, lvl ) AS (    SELECt ROWID,UPPER( name ),value,REGEXP_SUBSTr( UPPER( name ), '[^+]+', 1, 1 ),REGEXP_SUBSTr( value, '[^+]+', 1, 1 ),1    FROM   your_table    WHERe  name IS NOT NULL    AND    value IS NOT NULL  UNIOn ALL    SELECt rid,name,value,REGEXP_SUBSTr( name, '[^+]+', 1, lvl + 1 ),REGEXP_SUBSTr( value, '[^+]+', 1, lvl + 1 ),lvl + 1    FROM   splitstrings    WHERe  lvl < LEAST(        REGEXP_COUNT( name, '[^+]+' ),        REGEXP_COUNT( value, '[^+]+' )      )  )  SELECt *  FROM   ( SELECt rid, col, val FROM splitstrings )  PIVOT  ( MAX( val ) FOR col IN ( 'A' AS a, 'B' AS b, 'C' AS c, 'D' AS d ) )) srcON ( src.rid = dst.ROWID )WHEN MATCHED THEN  UPDATe    SET A = COALESCE( src.A, dst.A ),        B = COALESCE( src.B, dst.B ),        C = COALESCE( src.C, dst.C ),        D = COALESCE( src.D, dst.D );

输出 ：

SELECT * FROM your_table;NAME    VALUE       A  B  C  D------- ----------- -- -- -- --a+b+c+d x2+x1+x3+x5 x2 x1 x3 x5a+d+c   y7+y3+y4    y7    y4 y3

Oracle安装程序 ：

CREATE TABLE your_source (  name  VARCHAr2(50),  value VARCHAr2(50));INSERT INTO your_source  SELECt 'a+b+c+d', 'x2+x1+x3+x5' FROM DUAL UNIOn ALL  SELECt 'a+d+c',   'y7+y3+y4'    FROM DUAL;CREATE TABLE your_destination (--  name  VARCHAr2(50),--  value VARCHAr2(50),  A VARCHAr2(20),  B VARCHAr2(20),  C VARCHAr2(20),  D VARCHAr2(20));

插入语句 ：

INSERT INTO your_destination (  A, B, C, D )  WITH splitstrings ( rid, name, value, col, val, lvl ) AS (    SELECt ROWID,UPPER( name ),value,REGEXP_SUBSTr( UPPER( name ), '[^+]+', 1, 1 ),REGEXP_SUBSTr( value, '[^+]+', 1, 1 ),1    FROM   your_source    WHERe  name IS NOT NULL    AND    value IS NOT NULL  UNIOn ALL    SELECt rid,name,value,REGEXP_SUBSTr( name, '[^+]+', 1, lvl + 1 ),REGEXP_SUBSTr( value, '[^+]+', 1, lvl + 1 ),lvl + 1    FROM   splitstrings    WHERe  lvl < LEAST(        REGEXP_COUNT( name, '[^+]+' ),        REGEXP_COUNT( value, '[^+]+' )      )  )  SELECt  A,B,C,D  FROM   ( SELECt rid,  col, val FROM splitstrings )  PIVOT  ( MAX( val ) FOR col IN ( 'A' AS a, 'B' AS b, 'C' AS c, 'D' AS d ) );

输出 ：

SELECt * FROM your_destinationA  B  C  D-- -- -- --x2 x1 x3 x5y7    y4 y3