将列拆分为多行

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：我发现其实我一直有一个部分在全选（我是个糟糕的测试者:(），上面的代码可以工作，但唯一的问题是它会丢失最后一个站点值，但我会尝试解决这个问题。

编辑3：现在它可以工作了。

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

- Balroq

---

可能是SQL查询将匹配多个范围的数字列表转换为值列表的重复问题。 - APC

请查看我对另一个可能重复的SO帖子的回答。这是一个适用于您问题的10g解决方案。 - APC

我觉得现在我的选择功能好了一些。 - Balroq

在 SQL Server 2008 中有没有一种方法可以做到这一点？ - Necronet

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

- Balroq

Answer 2

你尝试过Michael Sofaer在如何最好地在Oracle 9i中拆分csv字符串的答案吗？

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;

- Conrad Frix

Answer 3

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

CREATE TABLE resulTable(

cnumber NUMBER,

Site VARCHAR2(1000)

);

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

/这里我用s2ss78s替换了数字，以便使用DBMS_UTILITY.comma_to_table（它不能处理数字）/

CREATE OR REPLACE PROCEDURE split_list_to_rows(num NUMBER, plist VARCHAR2) AS

ptablen BINARY_INTEGER;

ptab DBMS_UTILITY.uncl_array;

BEGIN

DBMS_UTILITY.comma_to_table (

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

tablen => ptablen,

tab => ptab);

FOR i IN 1 .. ptablen LOOP

INSERT INTO resulTable VALUES (num,REPLACE(LTRIM(ptab(i),'s'),'ss','-'));

END LOOP;

END;

------------针对每一行执行过程的PL/SQL块-------------------

开始

对于i在（从Sitetable中选择cnumber和Site）中进行循环

循环

split_list_to_rows(i.cnumber,i.Site);

结束循环;

结束;

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

从结果表中选择*;

- kupa

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)

- lokori

Answer 5

使用交叉连接：

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

- CoffeGirl