Baumzusammenstellung mit Hilfe von SQL (MS SQL)

[omata]

Ich habe gerade festgestellt, dass der angegebene Link nicht mehr geht.

Hier ein Beispiel mit Ideen von da...

SQL-Code:

CREATE TABLE [Tree] (
   NodeID int NOT NULL,
   ParentNodeID int NULL,
   NodeName varchar (50) COLLATE Latin1_General_CI_AS NOT NULL,
   Reihenfolge int NULL,
   CONSTRAINT PK_Tree PRIMARY KEY CLUSTERED (NodeID) ON PRIMARY,
   CONSTRAINT FK_Tree_Tree FOREIGN KEY (ParentNodeID) REFERENCES Tree (NodeID)
) ON PRIMARY

SQL-Code:

CREATE PROCEDURE GetSortedSubTree(@NodeID INT)
AS
DECLARE @IDS   TABLE (id INT PRIMARY KEY, Depth INT, NodePath VARCHAR(1000))
DECLARE @Depth INT
DECLARE @Done  BIT

SET @Depth = 1
SET @Done = 0

INSERT @IDS(id, Depth, NodePath) 
SELECT NodeID, @Depth, CONVERT(VARCHAR(1000), Reihenfolge)
FROM Tree 
WHERE NodeID = @NodeID

IF @@ROWCOUNT = 0 SET @Done = 1 
WHILE @Done = 0 BEGIN
  SET @Depth = @Depth + 1
  INSERT @IDS(id, Depth, NodePath)
  SELECT NodeID, @Depth, ids.NodePath + '|' + CONVERT(VARCHAR(1000), Reihenfolge)
  FROM Tree t
  INNER JOIN @IDS ids 
    ON ids.id = t.ParentNodeID 
  WHERE ids.Depth = @Depth-1
  IF @@ROWCOUNT = 0 SET @Done = 1 
END

SELECT t.NodeID, t.NodeName, ids.Depth, ids.NodePath
FROM Tree t
INNER JOIN @IDS ids 
  ON ids.id = t.NodeID
ORDER BY ids.NodePath

Gruss
Thorsten