SQL Snippets ™: Tutorials for Oracle Developers

create table t
(
  group_key varchar2(10),
  val       varchar2(10)
);

insert into t values ( 'Group 1' , 'a'  );

insert into t values ( 'Group 2' , 'a'  );
insert into t values ( 'Group 2' , 'b'  );

insert into t values ( 'Group 3' , 'a'  );
insert into t values ( 'Group 3' , 'b'  );
insert into t values ( 'Group 3' , 'c'  );

insert into t values ( 'Group 4' , 'a'  );
insert into t values ( 'Group 4' , 'a'  );
insert into t values ( 'Group 4' , 'b'  );
insert into t values ( 'Group 4' , 'b'  );

insert into t values ( 'Group 5' , 'a'  );
insert into t values ( 'Group 5' , 'b'  );
insert into t values ( 'Group 5' , null );
insert into t values ( 'Group 5' , 'd'  );
insert into t values ( 'Group 5' , 'e'  );

insert into t values ( 'Group 6' , null );

insert into t values ( 'Group 7' , null );
insert into t values ( 'Group 7' , null );

insert into t values ( 'Group 8' , 'a'  );
insert into t values ( 'Group 8' , 'a'  );
insert into t values ( 'Group 8' , 'b'  );
insert into t values ( 'Group 8' , 'b'  );
insert into t values ( 'Group 8' , null );
insert into t values ( 'Group 8' , null );

insert into t values ( null, null );

commit;

create table t_group_keys as select distinct group_key from t ;

create type varchar2_table_type as table of varchar2(10) ;
/
show errors

column val             format a10
column group_key       format a10
column string          format a15
column connect_by_path format a15

create table t2
(
  group_key varchar2(10) ,
  val       varchar2(10)
);

insert into t2
select group_key,  val
from   t2
model
  dimension by ( 0 as i, group_key )
  measures     ( val )
  ( val
    [ for i from 1 to 100 increment 1,
      for group_key like '%' from 1000 to 1999 increment 1
    ] = to_char( chr( mod(cv(i)-1,26)+97 ) )
  )
;