Sql query

[Alien]

Ok. I didn't understand from the question that there is a table.

Unfortunately, I can't run your query, since we're still on 8i. So I had to try something else.

 select str, substr(str,begin,end-begin) element
 from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1) end
 	   from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
 	   		 from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri  
			 	   from t
			      ) a
 			 ,    (select rownum rn, object_id 
			       from dba_objects) o 
			 where o.rn<=a.num_el
			 )
  	   );

SQL>  CREATE TABLE t
  2   ( str VARCHAR2(30) );

Tabel is aangemaakt.

SQL> INSERT INTO t VALUES ( 'X,Y,Z' );

1 rij is aangemaakt.

SQL> INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );

1 rij is aangemaakt.

SQL>  select str, substr(str,begin,end-begin) element
  2   from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1)
 end
  3       from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
  4          from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri  
  5          from t
  6           ) a
  7       ,    (select rownum rn, object_id 
  8            from dba_objects) o 
  9      where o.rn<=a.num_el
 10      )
 11        );

STR                            ELEMENT
------------------------------ ------------------------------
X,Y,Z                          X
X,Y,Z                          Y
X,Y,Z                          Z
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E

STR                            ELEMENT
------------------------------ ------------------------------
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F
XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG

13 rijen zijn geselecteerd.

Regards,

Arian

Edit: I realized later, that I used dba_objects as a matter of habit. Replace that with all_objects. In your case all_objects, or a custom-made table with sufficient rows would suffice. From 9i on, dual with a connect by is a safe replacement.