View source for Oracle Pivot and Merge

This example script shows how to get from rows to columns with PIVOT functionality - available from Oracle 11 and newer.

<syntaxhighlight lang=sql>
drop table t1;
drop table t2;

create index t1id on t1(id);
create index t2id on t2(id);
create index t2fk on t1(fk);

create table t1(id number, txt varchar2(20));
create table t2(id number, fk number, vkey varchar2(20), val varchar2(20));

insert into t1 values(1,'een');
insert into t1 values(2,'twee');
insert into t1 values(3,'drie');
insert into t1 values(4,'vier');

insert into t2 values(11,1,'val1','een-een');
insert into t2 values(12,1,'val2','een-twee');
insert into t2 values(13,1,'val3','een-drie');

insert into t2 values(22,2,'val1','twee-een');
insert into t2 values(22,2,'val2','twee-twee');
insert into t2 values(23,2,'val3','twee-drie');
insert into t2 values(24,2,'val4','twee-vier');

insert into t2 values(31,3,'val1','drie-een');
insert into t2 values(32,3,'val2','drie-twee');

insert into t2 values(41,4,'val1','vier-een');

alter table t1 add val1 varchar2(20);
alter table t1 add val2 varchar2(20);
alter table t1 add val3 varchar2(20);
alter table t1 add val4 varchar2(20);

-- Oracle 11+ PIVOT

select * 
  from ( select fk,vkey,val from t2 ) t2
  pivot ( max(val) for vkey in (
     'val1' as val1
    ,'val2' as val2
    ,'val3' as val3
    ,'val4' as val4) )
  ;
  
-- Merge into
  
merge into t1 using (
    select * 
      from ( select fk,vkey,val from t2 )
      pivot ( max(val) for vkey in (
         'val1' as val1
        ,'val2' as val2
        ,'val3' as val3
        ,'val4' as val4) )
     ) t2 ON ( t2.FK = t1.ID )
  WHEN MATCHED THEN UPDATE SET
       val1 = t2.VAL1
     , val2 = t2.VAL2
     , val3 = t2.VAL3
     , val4 = t2.VAL4
     ;
          
select * from t1;

</syntaxhighlight>

[[Category:Oracle]]
[[Category:201910]]