Roll Up an Org Tree With a Recursive CTE and a Real Cycle Guard
Use a recursive CTE to traverse an org tree while tracking depth, path, and cycle safety.
You need every employee beneath each executive, along with depth and a safe path, even though bad HR imports can create loops in the manager chain. Seed the hierarchy with the correct roots, recurse one manager-child edge at a time, and track visited IDs so cycles become explicit data instead of infinite recursion. The common trap is to trust the source system as a perfect tree. One bad back-reference turns that trust into an outage or a misleading rollup. Step 1 Write the anchor member to select the executive root rows you want to roll up, initializing depth to 0…
Sign up free — one personalized lesson every day, matched to your role and goals.
Already have an account? Sign in