oracle某字段值逗号分隔列转向行实现技巧
oracle某字段值逗号分隔列转为行实现技巧
select substr(x.col_1, x.pos1, x.pos2 - x.pos1 - 1)
from (select t.col_1,
level as lv,
instr(',' || t.col_1 || ',', ',', 1, level) as pos1,
instr(',' || t.col_1 || ',', ',', 1, level + 1) as pos2
from (
/**将某字段，由逗行分隔的列值转成行**/
select wm_concat(targetCol) col_1 from tableName
) t
connect by level <= (length(t.col_1) -
length(replace(t.col_1, ',', ''))) + 1) x
select substr(x.col_1, x.pos1, x.pos2 - x.pos1 - 1)
from (select t.col_1,
level as lv,
instr(',' || t.col_1 || ',', ',', 1, level) as pos1,
instr(',' || t.col_1 || ',', ',', 1, level + 1) as pos2
from (
/**将某字段，由逗行分隔的列值转成行**/
select wm_concat(targetCol) col_1 from tableName
) t
connect by level <= (length(t.col_1) -
length(replace(t.col_1, ',', ''))) + 1) x