|
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:
(comments are locked)
|
|
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
(comments are locked)
|


what is the version of SQL Server are you using ?
SQL 2008. I've written this query so it works in 2005 and possibly 2000 as perhaps there was a solution that wasn't version dependent. I'm happy to use a 2008 specific solution though.