All Forums Database
tranlm 3 posts Joined 12/15
25 Jul 2016
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.
1      E1
2      E1         A1
3                 A1        P1
4                           P1
5      E2         A2           
6                 A2
7                           P2
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 
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....


You must sign in to leave a comment.