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 

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) 
           )

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
                 )

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