One way for str2table(zt asktom)

Mohammad -- Thanks for the question regarding "list all user(s) group by group_list", version 8.1.7
originally submitted on 27-Jun-2003 10:46 Eastern US time, last updated 21-Apr-2005 12:02

Tom's latest followup | GOTO a Bookmarkable Page | Bottom

You Asked (Jump to Tom's latest followup)

Hello Tom,

i have the follwing table:

LOGIN_NAME      GROUP_LIST
--------------- --------------------------------------------------
Demo            1; 9000; 1000;
Koo             1000;
Feh             1000; 1006; 1021; 1030;
Hene            1006; 1000; 1021;
Jaehrling       1000;
Koell           1000;
Woeh            1000; 1001;


Now I am trying to write a query to get the below results

Demo    1
Demo    9000
Demo    1000
.
..

Could you plaese help me to solve this problem

Best regards
Mohammad

and we said...

oh yuck.  hate it when people do that.  "relational" database... ok

ops$tkyte@ORA817DEV> create or replace type myArray as table of number
  2  /

Type created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2table( p_string in varchar2 
) return myArray
  2  as
  3          l_data   myArray := myArray();
  4          l_string long := p_string;
  5          l_n      number;
  6  begin
  7          while (l_string is not null)
  8          loop
  9                  l_n := instr( l_string, ';' );
 10                  if ( l_n = 0 )
 11                  then
 12                          l_n := length(l_string)+1;
 13                  end if;
 14                  l_data.extend;
 15                  l_data(l_data.count) := substr( l_string, 1, l_n-1 );
 16                  l_string := substr( l_string, l_n+1 );
 17          end loop;
 18          return l_data;
 19  end;
 20  /

Function created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop table t;

Table dropped.

ops$tkyte@ORA817DEV> create table t ( login_name varchar2(10), group_list 
varchar2(200) );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t values ( 'demo', '1; 9000; 1000;' );

1 row created.

ops$tkyte@ORA817DEV> insert into t values ( 'feh', '1000; 1006; 1021; 1030;' );

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select t.login_name, t2.column_value
  2    from t, TABLE(cast( str2table(t.group_list) as myArray )) t2
  3  /

LOGIN_NAME COLUMN_VALUE
---------- ------------
demo                  1
demo               9000
demo               1000
feh                1000
feh                1006
feh                1021
feh                1030

7 rows selected.


is one way. 

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796605/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796605/