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
Split column to multiple rows
提问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, ',');