将列拆分为多行 [英] Split column to multiple rows

问题描述

我有一个表，该表的列包含用逗号(，)分隔的多个值，并且想将其拆分，因此我将earch Site放在其自己的行上，但前面具有相同的Number.

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

创建此输出

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:我发现实际上一直有一部分工作在进行选择(我曾经是一个糟糕的测试员:()，上面的一个工作了，但唯一的问题是它失去了最后的Site值，但我会尝试工作)一点..

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..

Edit3:现在可以正常工作

Now its working

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

推荐答案

正确的答案是.

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

这篇关于将列拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！