Output hierarchy ("complete path to leaf")

Transact-SQL
1

Hello,
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
sth_Weird

2

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

1 Like
3

Thank you for your reply. The solution works and the article in the link (as well as the comments to it) is brilliant!
sth_Weird