tel int,

firstname varchar(255),

lastname varchar(255),

Customer_ID varchar(255)

);

insert into PersonsInitial(tel,firstname,lastname,Customer_ID) values

('01234','Manu','Johns','456'),

('01234','Manu','Johns','C456'),

('21234','Fernand','Wajk','C389'),

('13554','Mathieu','Wainers','4683');

select distinct tel, firstname, lastname, customer_id from PersonsInitial

CREATE TABLE PersonsFinal (

tel int,

firstname varchar(255),

lastname varchar(255),

Customer_ID varchar(255)

);

insert into PersonsFinal(tel,firstname,lastname,Customer_ID)

select distinct pi1.tel, pi1.firstname, pi1.lastname, coalesce(pi.Customer_ID, pi1.Customer_ID) Customer_Id

from PersonsInitial pi1

outer apply (select *

from PersonsInitial pi2

where pi1.tel = pi2.tel

and pi1.firstname = pi2.firstname

and pi1.lastname = pi2.lastname

and pi2.Customer_ID like 'C%') pi;

select * from PersonsFinal