I have a table that contains an id an a parent-id. the parent-id of the highest level is null. How can I get the complete path to a certain leaf? Actually the structure can be compared to a folder-tree. Each record = folder knows the name of itself, what I need is the complete path.
I was wondering if it is possible to output that path using a query (and how to of course).
Thank you in advance
This can be achieved with use of a recursive CTE (rCTE), although as a caveat the performance will drop with bigger datasets
--Test Table DECLARE @Hier TABLE ( EmpID INT NOT NULL, Employee VARCHAR(100) NOT NULL, ManID INT NULL ) --Populate with some test data INSERT INTO @Hier (EmpID,Employee,ManID) VALUES (1,'Bob',NULL), (2,'Tom',1), (3,'Paul',1), (4,'Dan',1), (5,'Joe',4); --Show results SELECT * FROM @Hier AS H; --Use rCTE to calculate WITH HierBase (EmpID,Employee,ManID,Lvl,Hier) AS ( --Anchor Query/starting point SELECT H.EmpID, H.Employee, H.ManID, 0, CAST(H.Employee + '|' AS VARCHAR(8000)) FROM @Hier AS H WHERE ManID IS NULL UNION ALL --Recursive element SELECT H1.EmpID, H1.Employee, H1.ManID, HB.Lvl + 1, HB.Hier + CAST(H1.Employee + '|' AS VARCHAR(8000)) FROM @Hier AS H1 INNER JOIN HierBase AS HB ON H1.ManID = HB.EmpID ) SELECT H.EmpID, H.Employee, H.ManID, H.Lvl, H.Hier FROM HierBase AS H WHERE H.Employee = 'Joe';
Some additional reading and other methods can be found: The Performance of Traversing a SQL Hierarchy
Thank you for your reply. The solution works and the article in the link (as well as the comments to it) is brilliant!