将列拆分为多行

Question

我有一个表，其中有一列包含用逗号（，）分隔的多个值，并且希望将其拆分，以便我将每个站点放在其自己的行上，但前面的数字相同。

所以我的选择将从这个输入

table Sitetable

Number             Site
952240             2-78,2-89                                                                                                                                                                      
952423             2-78,2-83,8-34

创建这个输出

Number             Site
952240             2-78
952240             2-89
952423             2-78 
952423             2-83
952423             8-34

我发现了一些我认为可以工作但没有的东西..

select Number, substr(
    Site, 
    instr(','||Site,',',1,seq),
    instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq+1) > 0

编辑2：我看到我实际上一直有一个部分在工作选择（我是一个蹩脚的测试人员:(），上面一个有效，但唯一的问题是它丢失了最后一个站点值，但我会尝试对此进行一些工作..

Edit3：现在它可以工作

select Number, substr(
Site, 
instr(','||Site,',',1,seq),
instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq) > 0

Answer 1

正确答案是。

select Number, substr(
Site, 
instr(','||Site,',',1,seq),
instr(','||Site||',',',',1,seq+1) - instr(','||Site,',',1,seq)-1)  Site
from Sitetable,(select level seq from dual connect by level <= 100) seqgen
where instr(','||Site,',',1,seq) > 0

Answer 2

您是否尝试过 Michael Sofaer 对 如何最好地拆分 csv 字符串的回答甲骨文9i

create or replace function splitter_count(str in varchar2, delim in char) return int as
val int;
begin
  val := length(replace(str, delim, delim || ' '));
  return val - length(str); 
end;

create type token_list is varray(100) of varchar2(200);

CREATE or replace function tokenize (str varchar2, delim char) return token_list as
ret token_list;
target int;
i int;
this_delim int;
last_delim int;
BEGIN
  ret := token_list();
  i := 1;
  last_delim := 0;
  target := splitter_count(str, delim);
  while i <= target
  loop
    ret.extend();
    this_delim := instr(str, delim, 1, i);
    ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1);
    i := i + 1;
    last_delim := this_delim;
  end loop;
  ret.extend();
  ret(i):= substr(str, last_delim + 1);
  return ret;
end;

Answer 3

------------创建结果表-------------------------------- -----------

创建表resulTable(

cnumber number,

Site varchar2(1000)

);

------------创建分割器过程-------------------------------- ------

/这里我将数字例如：2-78 替换为 s2ss78s 以便使用
DBMS_UTILITY.comma_to_table（不适用于数字）/

创建或替换过程 split_list_to_rows(num number,plist varchar2) 作为

ptablen BINARY_INTEGER;

ptab DBMS_UTILITY.uncl_array；

开始

DBMS_UTILITY.comma_to_table (

list => Replace(replace(CONCAT('s', plist),',',',s'),'-','ss'),

tablen => ptablen,

tab => ；

FOR i IN 1 .. ptablen 循环

插入结果表值 (num,replace(ltrim(ptab(i),'s'),'ss','-'));

结束循环；

结尾;

------------PL/SQL 块为每行执行过程--------------------

开始

i in (select cnumber,Site from Sitetable)

循环

split_list_to_rows(i.cnumber,i.Site);

结束循环；

结尾;

------------------------------------查看结果-------------------- -------------------

从结果表中选择*；

Answer 4

我认为您的最大限制为 100 个逗号分隔值，这是不必要的，但对您的情况可能没有害处。

相反，

from Sitetable,(select level seq from dual connect by level <= 100) 

这应该适用于任意数量的值（级别受存在的最大逗号数量限制）。

from Sitetable,(select level seq from dual connect by level <=  (select max((LENGTH(site)-LENGTH(REPLACE(site,',', '' ))) + 1) from sitetable)

Answer 5

使用交叉联接：

SELECT Number、SiteNumber
来自网站表
交叉应用 STRING_SPLIT(站点, ',');