I would like to concatenate contact1 & 2 and contact 3 & 4. I would like to show only distinct contacts for each person_id. However, there can be duplicate contacts for different person_ids.
Any feedback would be greatly appreciated. Please note that the performance of the query is very important here.
I'm looking for an Oracle SQL code. Thank you!
create table emp_contact(
person_id numeric,
contact1 varchar(50),
contact2 varchar(50),
contact3 varchar(50),
contact4 varchar(50)
);
insert into emp_contact values (1,'jill', 'jill, mary', 'amy, jack', 'david, abby, mike');
insert into emp_contact values (2,null, 'jill, mary', 'amy, jack', null);
insert into emp_contact values (3,null, null, null, null);
insert into emp_contact values (4,'mike', 'jill, mary, abby', 'amy, jack, jill', null);
Output:
