Retrieving values of a tree structure into a table
-
Hi, I have a table that has 2 columns as Child and Parent in the following form.
Child Parent
A NULL
B A
C A
D C
E C
F D
G NULL
The tree structure is as follows:
I need to have a Stored Procedure, that gives all the children and sub children of a parent node.
For example, if I pass A as the parameter to the SP, it gives the children as B and C. Then checks for B and C in the parent columns and gives the children D and E, then checks and finds D in the parent node thus giving F.
The total chidren of A are B,C(Direct childs),D,E(sub childs- i.e children of C) and F(child of D).
The total chidren of C are D,E and F.
The total chidren of D are F.
How can I create a SP that stores these values in a table format?
-
You will find the answer here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
Dan2010 (8/16/2010)
Hi, I have a table that has 2 columns as Child and Parent in the following form.Child Parent
A NULL
B A
C A
D C
E C
F D
G NULL
The tree structure is as follows:
I need to have a Stored Procedure, that gives all the children and sub children of a parent node.
For example, if I pass A as the parameter to the SP, it gives the children as B and C. Then checks for B and C in the parent columns and gives the children D and E, then checks and finds D in the parent node thus giving F.
The total chidren of A are B,C(Direct childs),D,E(sub childs- i.e children of C) and F(child of D).
The total chidren of C are D,E and F.
The total chidren of D are F.
How can I create a SP that stores these values in a table format?
Notice the kind of cruddy answers you're getting? That's because you need to do what Eugene suggested and read the article at the link he posted.
Me? I'm going to take pity on you because this is your first post and I think your last name starts with the letter "P". 😉 Most people on this forum like to test their code out before posting a coded answer but don't have the time to load your very unfriendly-to-import data. The article at the link that Eugene points you to tells you all about this as well as what to do about it. Like I said, it's your first post so I'm going to show you... here's how you SHOULD have posted your data...
--===== Conditionally drop the test table to make reruns easierIF OBJECT_ID('TempDB..#YourForest','U') IS NOT NULL
DROP TABLE #YourForest
--===== Create a test table similar to the OP's post
CREATE TABLE #YourForest
(Child CHAR(1), Parent CHAR(1))
--===== Populate the table with the OP's date like he should have.
INSERT INTO #YourForest
(Child, Parent)
SELECT 'A',NULL UNION ALL
SELECT 'B','A' UNION ALL
SELECT 'C','A' UNION ALL
SELECT 'D','C' UNION ALL
SELECT 'E','C' UNION ALL
SELECT 'F','D' UNION ALL
SELECT 'G',NULL
If you are actually are using SQL Server 2008, there's a thing called a "HierarchyID" datatype and folks are going to run you through the gin-mill trying to show you how to build it and then how to use all the bloody built in methods to "traverse" the hierarchy. All those methods are actually SQLCLR's behind the scenes and they need to be because they would stink for performance otherwise. 😉
Celko is spot on about nested sets. They're nasty fast (they'll even beat the CLR's) but a lot of people don't like them because they "think" they're difficult to maintain. Patently not true but perception is reality for most. Also, a lot of people don't want to take the "time" to learn something new... like how to convert an Adjacency List to the Nested Set Model.
If you look in Books Online 2005, they'll show you a method of using a recursive CTE method for supposedly expanding a hierarchy. It's pretty useless because its output is not in hierarchical order. A RBAR While loop would also make some pretty nasty sucking sounds for performance. That leaves us with two methods... one is a recursive CTE that supposedly processes a level at a time along with the "secret ingredient" hierarchy path like this...
--===== Expand the hierarchy in the correct order with the correct indentationWITH
cteExpand AS
( --=== This is a recursive CTE... and it sucks the life out of queries
SELECT anchor.Child,
1 AS Level,
CAST(anchor.Child AS VARCHAR(8000)) AS HierarchyPath
FROM #YourForest AS anchor
WHERE Parent IS NULL
UNION ALL
SELECT recur.Child,
cte.Level + 1 AS Level,
cte.HierarchyPath + '\' + CAST(recur.Child AS VARCHAR(8000)) AS HierarchyPath
FROM #YourForest AS recur
INNER JOIN cteExpand AS cte
ON cte.Child = recur.Parent
)
SELECT SPACE((Level-1)*2)+ Child AS Child, Level
FROM cteExpand
ORDER BY HierarchyPath
Lots of people like that because it looks "set-based". It isn't, but it looks that way. In fact, it's 5 times worse than a "Level Based" While Loop (Celko call's it "Lasagne Code" because it's layered spaghetti code) for CPU and DURATION and almost 20 times worse in Reads. My recommendation would be to use the following, instead. It's uglier but a whole lot more efficient...
--===== Seed the Hierarchy table with the top HierarchyLevelSELECT Child,
Parent,
1 AS Level,
CAST(Child AS VARCHAR(4000)) AS HierarchyPath
INTO #HierarchyWork
FROM #YourForest
WHERE Parent IS NULL
--===== Create and preset a HierarchyLevel counter.
DECLARE @CurrentLevel INT
SET @CurrentLevel = 1
--===== Determine the rest of the hierarchy
-- The loop processes sets of rows based on HierarchyLevel.
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1 --Started at 1
INSERT INTO #HierarchyWork
(Child, Parent, Level, HierarchyPath)
SELECT yf.Child,
yf.Parent,
@CurrentLevel AS HierarchyLevel,
hier.HierarchyPath + CAST(yf.Child AS VARCHAR(4000)) AS HierarchyPath
FROM #YourForest AS yf
INNER JOIN #HierarchyWork AS hier
ON yf.Parent = hier.Child
AND hier.Level = @CurrentLevel - 1
END
;
--===== Display the hierarcy as before
SELECT SPACE((Level-1)*2)+ Child AS Child, Level
FROM #HierarchyWork
ORDER BY HierarchyPath
It won't be as fast as the nested sets Celko talks about, but it's faster than the full blown procedural code he's talking about.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply