Collect all the nodes in a tree captured within a relational table in SQL Server

Question

I have a table with the following schema:

CREATE TABLE Feature 
(
    FeatureId INT IDENTITY(1,1),
    ParentFeatureId INT,
    TargetObjectType VARCHAR(64)
)

As you can see, it defines a tree of features. I am interested to collect all the descendants found under the given nodes.

My current solution seems rather awkward to me:

IF OBJECT_ID('tempdb..#features') IS NOT NULL
    DROP TABLE #features

SELECT 
    0 FeatureId, TargetObjectType, ParentFeatureID
INTO 
    #features
FROM 
    Feature 
WHERE 
    1 = 0

INSERT INTO #features
    SELECT 
        FeatureId, TargetObjectType, ParentFeatureID
    FROM 
        Feature
    WHERE 
        TargetObjectType IN ('Root1', 'Root2')

WHILE @@ROWCOUNT > 0
    INSERT INTO #features
        SELECT FeatureId, TargetObjectType, ParentFeatureID
        FROM Feature f
        WHERE ParentFeatureID IN (SELECT FeatureId FROM #features) 
          AND NOT EXISTS (SELECT 1 FROM #features WHERE FeatureId = f.FeatureId)

SELECT * FROM #features

Is there a more elegant way of doing it? Using a recursive CTE, maybe?

The volume of data is insignificant (a few thousand records at most). All the data represents a forest with just a few levels of hierarchy, say 7 at the most.

Take a look at http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy if you want help on writing the recursive CTE. — Giorgos Altanis, Apr 17 '17 at 16:19

EMUEVIL · Accepted Answer · 2017-04-17T16:41:27.780

Here's a recursive CTE that'll give you child nodes for whatever FeatureId you pass in as the parameter at the top. If you only want the children, change the WHERE clause in the first select statement of the cte to be WHERE e.ParentFeatureId = @lookupFeatureId instead of WHERE e.FeatureId = @lookupFeatureId

DECLARE @lookupFeatureId INT; SET @lookupFeatureId = 6;

WITH cte (FeatureId, ParentFeatureId, TargetObjectType, [Level])
AS
(
    SELECT e.FeatureId, e.ParentFeatureId, e.TargetObjectType, 0 AS Level
    FROM Feature AS e
    WHERE e.FeatureId = @lookupFeatureId

    UNION ALL

    SELECT e.FeatureId, e.ParentFeatureId, e.TargetObjectType, d.[Level] + 1
    FROM Feature AS e
    INNER JOIN cte AS d
    ON e.ParentFeatureId = d.FeatureId
)
SELECT FeatureId, ParentFeatureId, TargetObjectType, [Level]
FROM cte

Collect all the nodes in a tree captured within a relational table in SQL Server

1 Answers1