oracle查询数据，以符号分割，显示多条数据

Question

with tmp(name,l) as ( --实际数据
    select '李','画画' from dual union all
    select '张','吃饭/睡觉' from dual union all
    select '王','抽烟/喝酒/打球' from dual
)

select * from tmp;

我想要达到的结果

wan(name,l) as( --想要的查询数据
    select '李','画画' from dual union all
    select '张','吃饭' from dual union all
    select '张','睡觉' from dual union all
    select '王','抽烟' from dual union all
    select '王','喝酒' from dual union all
    select '王','打球' from dual
)

select * from wan;

Answer 1

with tmp(name,l) as ( --实际数据
    select '李','画画' from dual union all
    select '张','吃饭/睡觉' from dual union all
    select '王','抽烟/喝酒/打球' from dual
),
t(name,L,lvl) as
 (select name, regexp_substr(L, '[^/]+', 1, 1) L, 2 lvl
    from tmp
  union all
  select x.name, regexp_substr(x.L, '[^/]+', 1, t.lvl), t.lvl + 1
    from tmp x, t
   where t.name = x.name
     and regexp_count(x.L, '[^/]+') >= t.lvl)
select NAME, L from t;