I was recently answering a forum question about working with a hierarchy. The poster had a table of nodes but importantly, nodes could be disabled. If that happened, the children of the node would move up to their nearest parent that wasn't disabled.
That is, if their parent was disabled and their grandparent was also disabled, then their great-grandparent would become their effective parent.
I provided the following code example, and I hope it helps someone.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
USE tempdb; GO DROP FUNCTION IF EXISTS dbo.GetParentNode; GO CREATE FUNCTION dbo.GetParentNode ( @NodePath varchar(max) ) RETURNS int AS BEGIN /* SELECT dbo.GetParentNode('12/13/14'); SELECT dbo.GetParentNode('12/14'); */ DECLARE @ReturnValue int; DECLARE @StringToProcess varchar(max) = REVERSE(@NodePath); DECLARE @DelimiterLocation int; SET @DelimiterLocation = CHARINDEX('/', @StringToProcess); IF @DelimiterLocation > 0 BEGIN SET @StringToProcess = SUBSTRING(@StringToProcess, @DelimiterLocation + 1, LEN(@StringToProcess)); SET @DelimiterLocation = CHARINDEX('/', @StringToProcess); IF @DelimiterLocation = 0 BEGIN SET @ReturnValue = CAST(REVERSE(@StringToProcess) AS int); END ELSE BEGIN SET @ReturnValue = CAST(REVERSE(LEFT(@StringToProcess, @DelimiterLocation - 1)) AS int); END; END; RETURN @ReturnValue; END; GO DROP TABLE IF EXISTS dbo.Nodes; GO CREATE TABLE dbo.Nodes ( NodeID int, ParentNodeID int, NodeName nvarchar(20), IsDisabled bit ); INSERT dbo.Nodes ( NodeID, ParentNodeID, NodeName, IsDisabled ) VALUES (1, NULL, 'node-1', 0), (2, 1, 'node-2', 1), (3, 1, 'node-3', 0), (4, 2, 'node-4', 0), (5, 2, 'node-5', 0), (6, 3, 'node-6', 0), (7, 4, 'node-6', 0), (8, 4, 'node-6', 0); WITH AllNodes AS ( SELECT toplevel.NodeID, toplevel.IsDisabled, toplevel.ParentNodeID, 1 AS NodeLevel, toplevel.NodeName, CAST(toplevel.NodeID AS varchar(max)) AS NodePath FROM dbo.Nodes AS toplevel WHERE toplevel.NodeID = 1 UNION ALL SELECT n.NodeID, n.IsDisabled, n.ParentNodeID, an.NodeLevel + 1, n.NodeName, an.NodePath + CASE WHEN n.IsDisabled = 0 THEN '/' + CAST(n.NodeID AS varchar(max)) ELSE '' END FROM dbo.Nodes AS n INNER JOIN AllNodes AS an ON an.NodeID = n.ParentNodeID ) SELECT an.NodeID, an.IsDisabled, an.NodeName, an.ParentNodeID, an.NodeLevel, an.NodePath, dbo.GetParentNode(an.NodePath) AS TrueParentNodeID FROM AllNodes AS an WHERE an.IsDisabled = 0; DROP TABLE dbo.Nodes; GO |
The approach I took was to use a recursive CTE to traverse the nodes. As I passed through each node level, I'd add to a NodePath only if the node wasn't disabled. That way, I built up a NodePath of just the active nodes leading to the particular node.
To make it easier to then extract the parent node from the path, I added a function to do that. It reversed the string to make it easy to start at the end and find the nodes. It discarded the first node (the current node), then found the next node, reversed the string again, and returned it as an int.
You should be able to copy/paste the whole code and try it. I hope it helps someone or gives you some ideas.