ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 100 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 101 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 2, 200 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 2, 201 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table p ( x int primary key );
 
Table created.
 
ops$tkyte@ORA9IR2> create table c ( x references p, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert all
  2    when (rn = 1) then into p (x) values (x)
  3    when ( 1 = 1) then into c(x,y) values(x,y)
  4  select x, y, row_number() over (partition by x order by y) rn
  5    from t;
 
6 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from p;
 
         X
----------
         1
         2
 
ops$tkyte@ORA9IR2> select * from c;
 
         X          Y
---------- ----------
         1        100
         1        101
         2        200
         2        201
 
 

Define last name please -- which are the last names above.

if the last name is simply the word after that last blank, instr can be used to look for the last blank and that result in substr can return the last word



 
ops$tkyte@ORA9IR2> select substr( 'aaa bbb cccccc', instr( 'aaa bbb cccccc', ' ', -1 )+1 ) from dual;
 
SUBSTR
------
cccccc
 

i don't know what you mean this time.

if you want to know how many unique names there are that happen more than once:


ops$tkyte@ORA10G> create table names
  2  as
  3  select ename, job from scott.emp;
 
Table created.
 
ops$tkyte@ORA10G> insert into names select ename, job from scott.emp where rownum < 6;
 
5 rows created.
 
ops$tkyte@ORA10G> select count(count(*))
  2  from names
  3  group by ename, job
  4  having count(*) > 1;
 
COUNT(COUNT(*))
---------------
              5
 
pretend ename and job are your first/last names.  that shows there are 5 first/last name occurrences that happen more than once. 

really WISH you could have done the create table and inserts for me, but anyway.

ops$tkyte@ORA9IR2> create table p( f1 varchar2(10) primary key, f2 varchar2(10) );

Table created.

ops$tkyte@ORA9IR2> create table c( f1 references p, seq int, c varchar2(10), primary key(f1,seq) );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x varchar2(10), y varchar2(10), z varchar2(10) );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'xf1', 'c1,c2,c3', 'xf2' );

1 row created.

ops$tkyte@ORA9IR2> insert into t values ( 'yf1', 'c1,c2',    'yf2' );

1 row created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @str2tbl
ops$tkyte@ORA9IR2> create or replace type str2tblType as table of varchar2(30)
  2  /

Type created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType
  2  PIPELINED
  3  as
  4      l_str      long default p_str || p_delim;
  5      l_n        number;
  6  begin
  7      loop
  8          l_n := instr( l_str, p_delim );
  9          exit when (nvl(l_n,0) = 0);
 10          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11          l_str := substr( l_str, l_n+1 );
 12      end loop;
 13      return;
 14  end;
 15  /

Function created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert when (seq = 1) then into p(f1,f2) values (x,z)
  2         when (seq >=1) then into c(f1,seq,c) values (x,seq,c)
  3  select x, z, column_value c,
  4         row_number() over (partition by x order by column_value) seq
  5    from t t, table(str2tbl(t.y));

7 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from p;

F1         F2
---------- ----------
xf1        xf2
yf1        yf2

ops$tkyte@ORA9IR2> select * from c;

F1                SEQ C
---------- ---------- ----------
xf1                 1 c1
xf1                 2 c2
xf1                 3 c3
yf1                 1 c1
yf1                 2 c2

 

ops$tkyte@ORA10GR2> insert
  2    when (rn=1) then
  3      into mst ( soc ) values ( soc )
  4    when (rn>0) then
  5      into dtl ( soc, param ) values ( soc, param )
  6  select params.*, row_number() over (partition by soc order by param) rn
  7    from params
  8   where soc in ( 'SOC1', 'SOC3' )
  9  /

8 rows created.

ops$tkyte@ORA10GR2> select * from mst;

SOC
------------------------------
SOC1
SOC3

ops$tkyte@ORA10GR2> select * from dtl;

SOC                            PARAM
------------------------------ ------------------------------
SOC1                           SOC1-param1
SOC1                           SOC1-param2
SOC1                           SOC1-param3
SOC3                           SOC3-param1
SOC3                           SOC3-param2
SOC3                           SOC3-param3

6 rows selected.