I have a set of data which are directly or indirectly connected, example data set shown below in MT. id e, f, g, h, i, and j are connected through i since they are all in the subgroup 2 and/or 3; a, b, c, d and k are connected since they are in either subgroup 1 or/and 2. Because ids a to k are all directly or indirectly connected to each other, these need to be in one group A. Id i in subgroup 4 and 5, so i is connected to m and n in group 5, should be in a new group B, and so on. Recursive CTE might be a solution, but I couldn't get my head around it. I'd really appreciate if someone here can help me with this. Thank you.
This answer isn't my preferred way of doing this as I've had to resort to a loop, but it might help you, and I'll keep playing to see if there is a better option.
I had to do some pre-processing on the data, so I used CTEs to do this
The first CTE
from those I can then work out which sub-groups are related, so for example the pair a,k tells me that sub-group 1 is related to sub-group 2, and I get those in CTE
Then I materialize this data in an ordered fashion into
which is then simply joined back onto the original data to determine a supergroupid
The full code:
answered Nov 13 at 02:46 PM
Kev Riley ♦♦