GeekZilla

Handling hierachical structures in SQL

Dealing with tree structures in SQL can be tricky. A common solution is to have a self referencing table with a primary key and parent key column. This allows for any depth of tree and allows each 'node' to have zero or more child nodes.

Below is a stored proc that is a good base for selecting a sub-tree from any sort of single table hierachy.

The example comes from a tree of 'targets'. Hence the table contains TargetID as athe primary key and ParentTargetId as the parent id.

CREATE PROCEDURE [dbo].[GetSortedSubTree]( @TargetID bigint )
AS
DECLARE @IDS TABLE ( id int PRIMARY KEY, Depth int, NodePath varchar(1000) )
DECLARE @Depth int
SET @Depth=1
DECLARE @Done bit
SET @Done=0
-- insert top of tree
INSERT @IDS( id, Depth, NodePath ) 
SELECT TargetID, @Depth, CONVERT( VARCHAR(1000), TargetID )
FROM tblTargets WHERE TargetID = @TargetID or 
(ParentTargetId is null and @TargetId =0)

IF @@rowcount=0 
SET @Done=1 

WHILE @Done=0  
BEGIN    
    SET @Depth=@Depth+1    
    INSERT @IDS( id, Depth, NodePath )    
    SELECT TargetID, @Depth, ids.NodePath+'|'+CONVERT( VARCHAR(1000), TargetID )    
    FROM tblTargets t    
    JOIN @IDS ids ON ids.id = t.ParentTargetID     
    WHERE ids.Depth=(@Depth-1)    
    IF @@rowcount=0 
    SET @Done=1   
END
SELECT t.TargetID, t.TargetText, ids.Depth, ids.NodePath
FROM tblTargets t
JOIN @IDS ids ON ids.id = t.TargetID
ORDER BY ids.NodePath

Note that the node path is the limiting factor as regards to depth -= but even using a varchar(1000) allows for a considerable depth.

Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.

Comments

Add Comment

Latest Articles