Table Setup
Create TABLE source (accountno integer, cell number, site number, contact number);
Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);
Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,2226883696);
Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,3334023239);
Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,4442548632);
Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);
Create TABLE target (accountno integer, cell number, site number,
contact number, phone4 number, phone5 number, phone6 number);
Insert INTO target (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);
Create View
CREATE or REPLACE VIEW col_combine_v ( accountno, cell, site, contact) AS select accountno, cell, site, contact from source union all select accountno, cell, site, contact from target
View created.
Select from Target after Creation
Select * from Target
Pivot
select accountno, r1_cell, r1_site, r1_contact , r2_cell, r2_site, r2_contact
from (select s.*,row_number()over(partition by accountno order by accountno) rn
from col_combine_v s
)
pivot (
min(cell) cell,min(site) site, min(contact) contact
FOR rn
IN (1 R1,2 R2)
)
ACCOUNTNO | R1_CELL | R1_SITE | R1_CONTACT | R2_CELL | R2_SITE | R2_CONTACT | 3000030 | 8881234567 | 8881235678 | 8881236789 | 8881234599 | 8881235688 | 8881236777 |
---|
Select From View
select accountno, cell, site,contact
from (select s.*,row_number()over(partition by accountno order by accountno) rn
from col_combine_v s
)
ORA-00907: missing right parenthesisMore Details: https://docs.oracle.com/error-help/db/ora-00907
Merge
MERGE INTO target tgt
USING (select accountno, r1_cell, r1_site, r1_contact, r2_cell, r2_site, r2_contact
from (select s.*,row_number()over(partition by accountno order by accountno) rn
from col_combine_v s
)
pivot (
min(cell) cell, min(site) site, min(contact) contact
FOR rn
IN (1 R1,2 R2)
)
) src
ON (src.accountno = tgt.accountno)
WHEN MATCHED
THEN
UPDATE SET
tgt.phone4 =
CASE
WHEN src.r1_contact <> tgt.contact
AND src.accountno = tgt.accountno
THEN src.r1_contact
END,
tgt.phone5 =
CASE
WHEN src.r1_contact <> tgt.contact
AND src.r1_contact <> tgt.phone4
AND src.accountno = tgt.accountno
THEN src.r1_contact
END,
tgt.phone6 =
CASE
WHEN src.r1_contact <> tgt.contact
AND src.r1_contact <> tgt.phone4
AND src.r1_contact <> tgt.phone5
AND src.accountno = tgt.accountno
THEN src.r1_contact
END
Select from Target after Merge
select * from target