SQL>
SQL> create table myTable (
2 id varchar2(10),
3 animal varchar2(10),
4 group_id number,
5 percent number );
Table created.
SQL>
SQL> set feedback off
SQL>
SQL> insert into myTable values ('a','Cat',1,0.993);
SQL> insert into myTable values ('a','Cat',2,0.93);
SQL> insert into myTable values ('a','Dog',3,0.90);
SQL> insert into myTable values ('b','Cat',1,0.993);
SQL> insert into myTable values ('b','Dog',3,0.90);
SQL> set feedback on
SQL>
SQL> SELECT id, animal, group_id, s FROM myTable
2 SPREADSHEET PARTITION BY (id)
3 DIMENSION BY (animal, group_id)
4 MEASURES (percent s) IGNORE nav (s['Dog',-1]=s['Dog',1], s['Cat',-1]=s['Cat',1]);
ID ANIMAL GROUP_ID S
---------- ---------- ---------- ----------
a Cat 1 .993
a Cat 2 .93
a Dog 3 .9
b Cat 1 .993
b Dog 3 .9
a Cat -1 .993
a Dog -1 0
b Cat -1 .993
b Dog -1 0
9 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>