求写法，多谢

求写法，谢谢
比如一个table

name others

小A yyy#mmm#dd

怎么写可以使查询结果变成如下

小A yyy
小A mmm
小A dd

谢谢！！！


[解决办法]

SQL code

--这个WITH t1 AS (SELECT NAME, '#' || OTHERS OTHERS FROM temp)SELECT DISTINCT NAME,                substr(t1.others,                       instr(t1.others, '#', 1, LEVEL) + 1,                       decode(instr(t1.others, '#', 1, LEVEL + 1),                              0,                              length(OTHERS) + 1,                              instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s  FROM dual, t1CONNECT BY LEVEL <=           (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1 b WHERE b.name = t1.name)ORDER BY NAME;
[解决办法]
SQL> select * from ta;
  
NAME OTHERS
---- ----------
小A  yyy#mmm#dd
小B  zzz#nnn#ss
  
SQL>  
SQL> select distinct name,
 2         substr('#' || others || '#',
 3                instr('#' || others || '#'  , '#', 1, level) +1,
 4                instr('#' || others || '#'  , '#', 1, level + 1) -
 5                instr('#' || others || '#'  , '#', 1, level)-1) others
 6    from ta
 7  connect by level <= (length(others)-length(replace(others,'#')))/length('#') +1
 8  order by name;
  
NAME OTHERS
---- ------------------------
小A  dd
小A  mmm
小A  yyy
小B  nnn
小B  ss
小B  zzz
  
6 rows selected