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:
Post a Comment