SQL Snippets ™: Tutorials for Oracle Developers

create table t1
(
  group_key varchar2(10) ,
  pivot_key varchar2(10) ,
  val       varchar2(10)
);

insert into t1 values ( 'G0', 'P0', 'a'   );
insert into t1 values ( 'G0', 'P1', 'A'   );
insert into t1 values ( 'G0', 'P2', '1'   );
insert into t1 values ( 'G0', null, 'xyz' );

insert into t1 values ( 'G1', 'P0', 'b'   );
insert into t1 values ( 'G1', 'P1', 'B'   );
insert into t1 values ( 'G1', 'P2', null  );

insert into t1 values ( null, 'P0', 'c'   );
insert into t1 values ( null, 'P2', '3'   );
insert into t1 values ( null, null, null  );

commit ;

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

insert into t2 values ( 'G0', 'P0', 10   );
insert into t2 values ( 'G0', 'P1', 5    );
insert into t2 values ( 'G0', 'P1', 3    );
insert into t2 values ( 'G0', 'P2', 20   );
insert into t2 values ( 'G0', 'P2', 6    );

insert into t2 values ( 'G1', 'P1', 9    );
insert into t2 values ( 'G1', 'P1', null );
insert into t2 values ( 'G1', 'P2', null );
insert into t2 values ( 'G1', 'P2', 5    );

insert into t2 values ( 'G2',  null, 4   );
insert into t2 values ( 'G2',  null, 7   );
insert into t2 values ( 'G2',  null, 10  );

insert into t2 values ( null, 'P0', 15   );
insert into t2 values ( null, 'P2', null );
insert into t2 values ( null, null, null );

commit;

create table t3
(
  group_key varchar2(10) ,
  pivot_key varchar2(10) ,
  val       varchar2(10)
);

insert into t3 values ( 'G0', 'P0', 'a'   );
insert into t3 values ( 'G0', 'P0', 'b'   );
insert into t3 values ( 'G0', 'P0', null  );
insert into t3 values ( 'G0', 'P1', 'A'   );
insert into t3 values ( 'G0', 'P1', 'BB'  );
insert into t3 values ( 'G0', 'P1', 'CCC' );
insert into t3 values ( 'G0', 'P2', '1'   );
insert into t3 values ( 'G0', 'P2', '2222');
insert into t3 values ( 'G0', null, 'xyz' );
insert into t3 values ( 'G0', null, null  );

insert into t3 values ( 'G1', 'P0', 'a'   );
insert into t3 values ( 'G1', 'P0', 'b'   );
insert into t3 values ( 'G1', 'P0', 'c'   );
insert into t3 values ( 'G1', 'P2', '1'   );
insert into t3 values ( 'G1', 'P2', '2'   );

insert into t3 values ( null, 'P0', 'c'   );
insert into t3 values ( null, 'P1', 'C'   );
insert into t3 values ( null, null, null  );

commit;


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

insert into t5 values ( 'G0', null );

insert into t5 values ( 'G1', 'a'  );

insert into t5 values ( 'G2', 'a'  );
insert into t5 values ( 'G2', 'b'  );

insert into t5 values ( 'G3', 'a'  );
insert into t5 values ( 'G3', 'b'  );
insert into t5 values ( 'G3', 'c'  );

insert into t5 values ( 'G4', 'a'  );
insert into t5 values ( 'G4', 'b'  );
insert into t5 values ( 'G4', 'c'  );
insert into t5 values ( 'G4', 'd'  );

insert into t5 values ( 'G5', 'a'  );
insert into t5 values ( 'G5', 'b'  );
insert into t5 values ( 'G5', 'c'  );
insert into t5 values ( 'G5', 'd'  );
insert into t5 values ( 'G5', 'e'  );

insert into t5 values ( 'G6', 'a'  );
insert into t5 values ( 'G6', 'b'  );
insert into t5 values ( 'G6', 'c'  );
insert into t5 values ( 'G6', 'd'  );
insert into t5 values ( 'G6', 'e'  );
insert into t5 values ( 'G6', 'f'  );

insert into t5 values ( 'G7', 'a'  );
insert into t5 values ( 'G7', 'b'  );
insert into t5 values ( 'G7', 'c'  );
insert into t5 values ( 'G7', 'd'  );
insert into t5 values ( 'G7', 'e'  );
insert into t5 values ( 'G7', 'f'  );
insert into t5 values ( 'G7', 'g'  );

insert into t5 values ( 'G8', 'a'  );
insert into t5 values ( 'G8', 'b'  );
insert into t5 values ( 'G8', 'c'  );
insert into t5 values ( 'G8', 'd'  );
insert into t5 values ( 'G8', 'e'  );
insert into t5 values ( 'G8', 'f'  );
insert into t5 values ( 'G8', 'g'  );
insert into t5 values ( 'G8', 'h'  );

commit;

create table t6 as
select
  cast
    ( 'G'|| trunc( num/5 )
      as varchar2(10)
    ) as group_key,
  cast
    ( 'a' || mod(num,5)
      as varchar2(10)
    ) as val
from
  ( select  level - 1 as num
    from    dual
    connect by level <= 100000
  )
;