oracle 将列拆分为多行

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/3713107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 21:30:53  来源:igfitidea点击:

Split column to multiple rows

sqloracleoracle10gtokenize

提问by Balroq

I have table with a column that contains multiple values separated by comma (,) and would like to split it so I get earch Site on its own row but with the same Number in front.

我有一个表格,其中包含多个由逗号 (,) 分隔的值,我想将其拆分,因此我将每个站点放在自己的行上,但前面有相同的数字。

So my select would from this input

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

table Sitetable

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

Create this output

创建此输出

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

I found something that I thought would work but nope..

我发现了一些我认为可行的东西,但没有。

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

Edit2: I see that I have actually had a part working select all the time (I was a crappy tester :(), the above one works but the only problem is that it looses the last Site value but Ill try to work a bit on that..

Edit2:我看到我实际上一直有一部分工作在选择(我是一个蹩脚的测试员:(),上面的一个工作,但唯一的问题是它失去了最后一个站点值,但我会尝试工作一点那..

Edit3: Now its working

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

回答by Balroq

And the correct answer is.

而正确答案是。

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

回答by Conrad Frix

Did you Try Michael Sofaer's answer to How to best split csv strings in oracle 9i

您是否尝试过 Michael Sofaer 对How to best split csv strings in oracle 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;

回答by kupa

------------Create Result Table-------------------------------------------

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

create table resulTable(

创建表结果表(

cnumber number,

号码,

Site varchar2(1000)

站点 varchar2(1000)

);

);

------------Create Splitter Procedure--------------------------------------

------------创建拆分程序----------------------------------- ---

/Here I replaced numbers for example: 2-78 by s2ss78s for using
DBMS_UTILITY.comma_to_table(it doesn't work on numbers)
/

/这里我替换了数字,例如:2-78 by s2ss78s 用于使用
DBMS_UTILITY.comma_to_table(它不适用于数字)
/

create or replace procedure split_list_to_rows(num number,plist varchar2) as

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

ptablen BINARY_INTEGER;

ptablen BINARY_INTEGER;

ptab DBMS_UTILITY.uncl_array;

ptab DBMS_UTILITY.uncl_array;

begin

开始

DBMS_UTILITY.comma_to_table (

DBMS_UTILITY.comma_to_table (

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

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

tablen => ptablen,

tablen => ptablen,

tab => ptab);

标签 => ptab);

FOR i IN 1 .. ptablen LOOP

FOR i IN 1 .. ptablen 循环

insert INTO resulTable VALUES (num,replace(ltrim(ptab(i),'s'),'ss','-'));

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

END LOOP;

结束循环;

END;

结尾;

------------PL/SQL Block To Execute Procedure For Each Row-------------------

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

begin

开始

for i in (select cnumber,Site from Sitetable)

对于 i in(从 Sitetable 中选择 cnumber,Site)

loop

环形

split_list_to_rows(i.cnumber,i.Site);

split_list_to_rows(i.cnumber,i.Site);

end loop;

结束循环;

end;

结尾;

------------------------See The Result---------------------------------------

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

select * from resulTable;

从结果表中选择 *;

回答by lokori

I think you have a max limit of 100 comma separated values which is unnecessary, though probably not harmful in your case.

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

instead of

代替

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

this should work for any number of values (level limited by the max number of commas present).

这应该适用于任意数量的值(级别受当前逗号的最大数量限制)。

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

回答by Microhash

Use cross join:

使用交叉连接:

SELECT Number, SiteNumber
FROM Sitetable CROSS APPLY STRING_SPLIT(Site, ',');

SELECT Number, SiteNumber
FROM Sitetable CROSS APPLY STRING_SPLIT(Site, ',');