Oracle中的列转行例子详解

Oracle中的列转行例子详解
数据如下:
name id
张三 1,2,3

要求实现:
name id
张三 1
张三 2
张三 3

–创建临时表
create table tmp as(select ‘张三’ name, ‘1,2,3’ id from dual);
–写法1
select name,regexp_substr(id,’[^,]+’,1,level) as id
from tmp
connect by level <= regexp_count(id,’,’)+1
order by id

–写法2：
select name,trim(regexp_substr(id,’[^,]+’,1,level)) as id
from tmp
connect by name = prior name
and prior dbms_random.value is not null
and level <= length(regexp_replace(id, ‘[^,]’))+1;

–写法3
select name,
–regexp_replace(id,’(\w+),(\w+),(\w+)’,level) id
regexp_replace(id,’(\w+),(\w+),(\w+)’,’’||to_char(level)) id
from tmp
connect by level <= regexp_count(id,’,’)+1;

–写法4：
select name,
substr(’,’||id||’,’,instr(’,’||id||’,’, ‘,’, 1, level) + 1, instr(’,’||id||’,’, ‘,’, 1, level + 1) -( instr(’,’||id||’,’, ‘,’, 1, level) + 1))
from tmp
connect by level <= regexp_count(id,’,’)+1
order by level