SQL: Find nearest enabled parent in a hierarchy using T-SQL

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.

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.

Leave a Reply

Your email address will not be published.