Hierarchy in SQL

Posted on June 7th, 2008 in Coding, Work by abdallah

from this blog post

  1. CREATE TABLE Tree (
  2. Node int NOT NULL IDENTITY(100, 1),
  3. ParentNode int,
  4. EmployeeID int NOT NULL,
  5. Depth tinyint,
  6. Lineage varchar(255) )
  1. UPDATE Tree SET Lineage=‘/’, Depth=0 WHERE ParentNode IS NULL
  1. WHILE EXISTS (SELECT * FROM Tree WHERE Depth IS NULL)
  2.    UPDATE T SET T.depth = P.Depth + 1,
  3.    T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + ‘/’
  4.    FROM Tree AS T
  5.    INNER JOIN Tree AS P ON (T.ParentNode=P.Node)
  6.    WHERE P.Depth>=0
  7.    AND P.Lineage IS NOT NULL
  8.    AND T.Depth IS NULL
  1. SELECT Space(T.Depth*2) + E.Name AS Name
  2. FROM Employees E
  3. INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
  4. ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

Post a comment

You must be logged in to post a comment.