The select is supposed to be this (I had a typo in the first post): select t1.id, t2.column_value num from (select id, split_string (value) v from t) t1 natural join table (t1.v) t2;
On Fri, Mar 5, 2010 at 2:29 PM, Joel <[email protected]> wrote: > Do you have any comments about this implementation (10.2+)? Do you > know of better ways to do this? Does this one have any major issues > in your opinion? > Thanks, Joel > (PS -Sorry about the extra PM on this one David. Tried to reply to > the old message but hit wrong reply button accidentally.) > > create or replace type split_type is table of varchar2 (4000); > > create or replace function split_string (p_string in varchar2) > return split_type > as > l_data split_type := split_type (); > cursor c_list > is > select trim (both ' ' from regexp_substr (str, '[^,]+', 1, > level)) as single_element > from (select p_string str from dual) > connect by instr (str, ',', 1, level - 1) > 0; > begin > for list_rec in c_list > loop > l_data.extend; > l_data (l_data.count) := list_rec.single_element; > end loop; > return l_data; > end; > > create table t (id number, value varchar2(255)); > insert into t values (1,'one,two,three'); > insert into t values (2,'four, five, six'); > insert into t values (3,'seven,eight, nine '); > commit; > > select * from t; > > select t1.id, t2.column_value num > from (select id, get_test_table (value) v from t) t1 natural join > table (t1.v) t2; > > drop table t purge; > > > > >On Dec 13, 9:03 am, Joel <[email protected]> wrote: > > > >> There are new functions pivot, unpivot, and listagg. Any idea how to > >> unlistagg? > > > >Not without parsing the output with, say, instr(). The listagg > >function is intended to generate report output, imo, not to generate > >data for loading elsewhere. > > > >David Fitzjarrell > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
