The Lost Notebook

from this blog post

CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )

UPDATE Tree SET Lineage='/', Depth=0 WHERE ParentNode Is Null

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
   UPDATE T SET T.depth = P.Depth + 1, 
   T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
   FROM Tree AS T 
   INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
   WHERE P.Depth>=0 
   AND P.Lineage Is Not Null 
   AND T.Depth Is Null

SELECT Space(T.Depth*2) + E.Name AS Name
FROM Employees E
INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

Similar Posts:

• Bypassing stupid security measures
• MySQL Incremental Backups