partition by a superset of multiple columns

Database
N/A

partition by a superset of multiple columns

Hi guys,

 

Hoping a knowledgeable pro can help me with a problem I'm stuck on. I have a table with multiple columns for different values, with an example given below.

ID    EMAIL    ADDRESS    PHONE  

1      E1

2      E1         A1

3                 A1        P1

4                           P1

5      E2         A2           

6                 A2

7                           P2

 

etc...

where the letters are just placeholders for actual observed (matching) values. The blanks may be NULL, or they may just be different values that don't match.

What I'd like is to assign block numbers for superset groupings, depending on if ANY of the column values match. So, as the output I'm hoping for 

ID    EMAIL    ADDRESS    PHONE    BLOCK

1      E1                            1

2      E1         A1                 1

3                 A1        P1       1

4                           P1       1

5      E2         A2                 2

6                 A2                 2

7                           P2       3

 

etc...                               etc...

Can anyone help me with this? I've looked into partition by, group by, group set, rollup, cube, etc. but can't find an approach that works.

Thank you ahead of time. I'll be keeping my eye out in case dnoeth can help me....