根据2列填充列 [英] populate columns based on 2 columns

问题描述

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

Hoping someone can help me write an SQL script or function.

我有一个数据源，我希望函数可以通过该数据源并根据指定的2列填充相关列.

I have a data source which i want the function to go through and populate the relevant columns based on the 2 specified columns.

所以名称和值列.

例如

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

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

therefore the the script/function should populate column a,b,c,d with values 2,1,3,5 respectively for every row of the data source..

这可能吗?如果是这样，有人可以告诉我怎么做.

is this possible? if so, can someone show me how.

推荐答案

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

版本2:

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

这篇关于根据2列填充列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！