Join a table to itself with no specfic ID

[teebag]

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|[e-mail]|4321|
J*SOAP|[e-mail]||
J*SOAP||4321|4
J*SOAP|[e-mail]||2
J*DOE||12345|3

Output:
INIT_SURNAME|EMAIL|PHONE|OTHERID
J*SOAP|[e-mail]|4321|4
J*SOAP|[e-mail]||2
J*DOE||12345|3

Can this be done?

[rch1231]

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

[teebag]

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

[rch1231]

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.