Wednesday, March 3, 2010

SQL Tree Hierarchy

How to construct the tree hierarchy using SQL???
I was searching for this and found a great link, so I followed and it works!

Here's the snippet for my codes:
declare @AITree table 
(Node int NOT NULL IDENTITY(100, 1), AIType int,
parent varchar(20), child varchar(20), 
depth int NULL, lineage varchar(1000))

--insert whatever inside your table, u can ignore the AIType
insert into @AITree (AIType, child)
select AIType, ID from AI_Grouping 
WHERE effstartdate<=@treeDate and effendDate>=@treeDate
order by AIType, parent, child

--updating parent using the node (not my table)
UPDATE T
SET T.Parent=P.node
FROM @AITree T 
INNER JOIN AI_Grouping C ON T.child=C.ID and T.AIType = C.AIType 
INNER JOIN AI_Grouping PT ON C.parent=PT.child and C.AIType = PT.AIType 
INNER JOIN @AITree P ON PT.ID=P.child and P.AIType = PT.AIType 
WHERE C.effstartdate<=@treeDate and C.effendDate>=@treeDate AND
PT.effstartdate<=@treeDate and PT.effendDate>=@treeDate 

-- updating the top parent
UPDATE @AITree SET Lineage='/', Depth=0 WHERE parent Is Null

-- updating the rest of the nodes
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' 
FROM @AITree AS T 
INNER JOIN @AITree AS P ON (T.Parent=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

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

--try this to output the treee
SELECT Space(T.Depth*2) + 
    case depth when 0 then '-' else '' end + 
    G.child AS Name
FROM AI_Grouping G INNER JOIN @AITree T 
ON G.ID = T.child 
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

Enjoy!
Reference: here

No comments: