For a finite number of levels, probably. For an unknown number of levels, no.

The recursive CTE you used has the problem of needing to recalculate the tree for whatever you're looking up every time it's used. That's probably ok if the underlying data is changing all the time. But, if the data is relatively static, then using "Nest Set Hierarchies" is probably the better way to go. The queries for downlines, uplines, and hierarchical aggregations are lightning fast because of the way they're done.

Here's a link for the Nest Set things... a GOOGLE of the subject will return more information on the subject that you can imagine... some of it is also pretty useless... 😛

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

If memory serves, the code isn't written for SQL Server and can stand some optimizations there. Also (if I remember correctly), the code that converts from the Adjacency Model to the Nested Set model has a small bug in it... it always leaves off the last node. I don't remember exactly what I did to fix it but I do remember the fix being fairly easy to find and repair.

As a side bar, when I do have to do such a thing as hierarchies, I tend to embed the Nested Set model into the Adjacency Model so that I have the best of both worlds.

There's a third way to do such things using a column that contains the entire "upline" path for each node. It's also quite effective especially for stable hierarchies. Here's some working example code for that method...

--=======================================================================================

-- Setup some test data... note that nothing in this section is part of the actual

-- solution.

--=======================================================================================

--===== Setup a "quiet" environment

SET NOCOUNT ON

--===== Create a table to hold some test data.

-- This is NOT part of the solution

CREATE TABLE #yourtable

(

ID INT,

ParentID INT,

Descrip VARCHAR(20)

)

--===== Populate the test table with 2 "trees" of data

INSERT INTO #yourtable

(ID,ParentID,Descrip)

SELECT 9,NULL,'County 1' UNION ALL --Note NULL, this is top node of "Tree 1"

SELECT 2,9 ,'C1 Region 1' UNION ALL

SELECT 4,9 ,'C1 Region 2' UNION ALL

SELECT 3,2 ,'C1 R1 Unit 1' UNION ALL

SELECT 5,2 ,'C1 R1 Unit 2' UNION ALL

SELECT 6,4 ,'C1 R2 Unit 1' UNION ALL

SELECT 7,NULL,'County 2' UNION ALL --Note NULL, this is top node of "Tree 2"

SELECT 8,7 ,'C2 Region 1' UNION ALL

SELECT 1,9 ,'C1 Region 3'

--=======================================================================================

-- The following code makes a Hierarchy "sister" table with strings that are used

-- to traverse various hierarchies.

--=======================================================================================

--===== Create and seed the "Hierarchy" table on the fly

SELECT ID,

ParentID,

Descrip,

Level = 0, --Top Level

HierarchyString = CAST(STR(ID,5) AS VARCHAR(8000))+' '

INTO #Hierarchy

FROM #yourtable

WHERE ParentID IS NULL

--===== Declare a local variable to keep track of the current level

DECLARE @Level INT

SET @Level = 0

--===== Create the hierarchy in the HierarchyString

WHILE @@ROWCOUNT > 0

BEGIN

SET @Level = @Level + 1

INSERT INTO #Hierarchy

(ID, ParentID, Descrip, Level, HierarchyString)

SELECT y.ID,y.ParentID,y.Descrip, @Level, h.HierarchyString + STR(y.ID,5) + ' '

FROM #yourtable y

INNER JOIN #Hierarchy h

ON y.ParentID = h.ID --Looks for parents only

AND h.Level = @Level - 1 --Looks for parents only

END

--=======================================================================================

-- Now, demo the use of the sister table

--=======================================================================================

--===== Display the entire tree with indented descriptions according to the Level

SELECT ID,

ParentID,

Level,

LEFT(REPLICATE(' ',Level*2)+descrip,30),

HierarchyString

FROM #Hierarchy

ORDER BY HierarchyString

--===== Select only the "downline" for ID 2 including ID 2

SELECT ID,

ParentID,

Level,

LEFT(REPLICATE(' ',Level*2)+descrip,30),

HierarchyString

FROM #Hierarchy

WHERE HierarchyString LIKE '% 2 %'

ORDER BY HierarchyString

drop table #Hierarchy

drop table #yourtable

Obviously, you wouldn't drop the tables once created. For relatively static tables, you'd rerun the code when rows were added, deleted, or modified.