Join a table to itself with no specfic ID
I have a table of customer records which has no ID column, a customer may appear in the table more than once with various of the columns populated. I have managed to build a column INIT_SURNAME which is fully poplated but not unique to a customer. How can I merge the rows for matching customers where they have the same INIT_SURNAME and share one other common data element. For example I want to return 3 cutomers from the following table, the 1st 3 rows are the same person.
e.g
INPUT:
INIT_SURNAME|EMAIL|PHONE|OTHERID
J*SOAP|js@x.com|4321|
J*SOAP|js@x.com||
J*SOAP||4321|4
J*SOAP|otherjoe@y.com||2
J*DOE||12345|3
Output:
INIT_SURNAME|EMAIL|PHONE|OTHERID
J*SOAP|js@x.com|4321|4
J*SOAP|otherjoe@y.com||2
J*DOE||12345|3
Can this be done?