根据2列填充列

Question

希望有人可以帮助我编写SQL脚本或函数。

我有一个数据源，我希望该功能通过并基于2个指定的列填充相关列。

因此，名称和值列。

例如

name= A+b+c+d 
Value= 2+1+3+5

因此，脚本/函数应该为数据源的每一行分别在a，b，c，d列中填充值2,1、3、5。

这可能吗？如果是这样，有人可以教我如何做。

Answer 1

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 dst
USING (
  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 ) )
) src
ON ( 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 x5
a+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 ( /* name, value, */ 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 /* name, value, */ A,B,C,D
  FROM   ( SELECT rid, /* name, value, */ 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_destination

A  B  C  D
-- -- -- --
x2 x1 x3 x5
y7    y4 y3