'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 Email
800000000 hello
777777777 unique
800000000 hi
555555555 hi

This is the desired output.

Phone Email 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 Email
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
Email 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