'SQL: I have a table with multiple identifiers. If any one identifier matches, I want it to roll up to the same master identifier
I have a table of unique identifier combinations that belong to the same person. Sometimes, a person can sometimes have multiple phone numbers and multiple emails. I would like to add a third column to the table that is a personid. If either the phone or the email matches the phone or email in another row, those rows should have the same personid. To give an example, if this is the table.
| Phone | |
|---|---|
| 800000000 | hello |
| 777777777 | unique |
| 800000000 | hi |
| 555555555 | hi |
This is the desired output.
| Phone | PersonID | |
|---|---|---|
| 800000000 | hello | 1 |
| 777777777 | unique | 2 |
| 800000000 | hi | 1 |
| 555555555 | hi | 1 |
If it was a one to many relationship, I would just use rank to give each phone or email a corresponding number. However, that only works one direction. I tried to give each column a unique rank or aggregating values that should be combined into an array, but I am stuck figuring out how to use the values to get the desired output
This is what I started with.
| Phone | |
|---|---|
| 800000000 | hello |
| 777777777 | unique |
| 800000000 | hi |
| 555555555 | hi |
I tried to get arrays of the values that match in the other column.
| Phone | Email Array |
|---|---|
| 800000000 | hello, hi |
| 777777777 | unique |
| 555555555 | hi |
| Phone Array | |
|---|---|
| hello | 800000000 |
| unique | 777777777 |
| hi | 800000000 , 555555555 |
But I can't figure out how to join them together.
I am specifically using snowflake.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
