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?

select distinct concat(INIT_SURNAME, EMAIL)
INIT_SURNAME,
EMAIL,
MAX(PHONE),
MAX(OTHERID)
from table
order by INIT_SURNAME, EMAIL
group by INIT_SURNAME, EMAIL

might just work but no promises...
I would love to know what happens...

Thanks but wont work, customer could join on phone or otherid as well as phone

Well it was pretty quick and dirty to begin with. Best suggestion I can make is to create a table from the data that does have an id and post non-null information information to it based on the your criteria. Then generate your report from the result table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.