I would like to know the most efficient way to create a group for objects related to one another. The data is in the form:
So 1 is related to 2 (and all objects are related to themselves) and 3 is related to 2. As both 1 and 3 are related to 2, I would like to know they are also related to each other. So the final output would be:
The group doesn't have to be called a letter, I've just used this as an example to show it's not an object id.
The solution I have come up with is below, but it doesn't seem as efficient as it could be and over complicated:
You could use an hierarchyid and do something like this:
CREATE TABLE #ObjectRelationship ( ObjectNode hierarchyid PRIMARY KEY CLUSTERED ,ParentNode AS ObjectNode.GetAncestor(1) PERSISTED ,[Level] AS ObjectNode.GetLevel() PERSISTED ,ObjectID int ) CREATE UNIQUE INDEX idxLevelObjectNode ON #ObjectRelationship([Level], ObjectNode) INSERT #ObjectRelationship (ObjectNode, ObjectID) SELECT '/1/',1 union SELECT '/1/2/',2 union SELECT '/1/5/',5 union SELECT '/1/6/',6 union SELECT '/1/2/3/',3 union SELECT '/1/2/6/',6 union SELECT '/1/2/3/4/',4 union SELECT '/1/2/3/10/',10 union SELECT '/1/2/3/4/5/',5 union SELECT '/1/2/3/4/5/7/',7 union SELECT '/9/',9 union SELECT '/9/10/',10 union SELECT '/15/',15 union SELECT '/15/16/',16 SELECT DISTINCT x.ObjectID, [GroupID] = DENSE_RANK() OVER(ORDER BY ParentNode) FROM #ObjectRelationship [ObjRel] CROSS APPLY (SELECT ObjectID FROM #ObjectRelationship WHERE 1 = ObjectNode.IsDescendantOf([ObjRel].ParentNode)) [x] WHERE [Level] = 2 ORDER BY GroupID, ObjectID DROP TABLE #ObjectRelationship