I have an issue on SQL Server. I have a table similar to this example:
+------------+----------------+--------+--------------------------+
| RegisterId | Name | Phone | Email |
+============+================+========+==========================+
| XXX-00001 | John Strauss | 241567 | Null |
| XXX-00023 | Rick Astley | 241567 | [email protected] |
| XXX-00003 | John Strauss | NULL | NULL |
| XXX-00099 | NULL | 241567 | [email protected] |
| XXX-00085 | NULL | 256819 | [email protected] |
| XXX-00016 | NULL | NULL | [email protected] |
| XXX-00007 | John Deep | 280933 | NULL |
| XXX-00008 | John Deep | 93484 | NULL |
| XXX-00009 | Javier Estrada | 94578 | [email protected] |
+------------+----------------+--------+--------------------------+
You can notice I have duplicated values. It’s because they correspond to the same user. This happen cause the values can come from different many dbs.
I would like to assign them a new ID to identify them as the same user (as it should be) if they have same phone, same name or same email.
Something like this:
+------------+----------------+--------+--------------------------+--------+
| RegisterId | Name | Phone | Email | UserId |
+============+================+========+==========================+========+
| XXX-00001 | John Strauss | 241567 | Null | 1 |
| XXX-00023 | Rick Astley | 241567 | [email protected] | 1 |
| XXX-00003 | John Strauss | NULL | NULL | 1 |
| XXX-00099 | NULL | 241567 | [email protected] | 1 |
| XXX-00085 | NULL | 256819 | [email protected] | 1 |
| XXX-00016 | NULL | NULL | [email protected] | 1 |
| XXX-00007 | John Deep | 280933 | NULL | 2 |
| XXX-00008 | John Deep | 93484 | NULL | 2 |
| XXX-00009 | Javier Estrada | 94578 | [email protected] | 3 |
+------------+----------------+--------+--------------------------+--------+
I have tried many functions in SQL, even some CTE’s but I just can’t find a way to achieve it. Could someone please give me an idea of which approximation should I use to solve this problem?
New contributor
Jesús Méndez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.