0

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?

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by rch1231
0

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

0

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

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.