Recursive query for heirarchy

  • I need a leg up on producing a resultset that'll go into a treeview on a web page.

    We have the following data.

    DealID      ParentID    
    ----------- ----------- 
    1029        NULL
    1030        1029
    1032        1030
    1033        1030
    1034        1030
    1035        1030
    1036        1030
    1037        1030
    1038        1032
    1039        1032
    1040        1037
    1031        1029
    1041        1037

    This is the code I've got so far. The @SplitID will eventually become a parameter to a stored procedure. It could be any of the ID numbers from the above list.

    BEGIN
    DECLARE @SplitID int
    SET @SplitID = 1035
    
    SET NOCOUNT ON
    SET DATEFORMAT dmy
    DECLARE @DealTree table (
    DealID int
    , ParentID int
    , lvl varchar(50)
    
    )
    
    DECLARE @TopID int
    DECLARE @DealID int
    DECLARE @CurrLvl int
    DECLARE @cnt int
    
    SET @CurrLvl = 0
    
    SELECT @DealID = ParentID
    FROM dbo.Deal 
    WHERE DealID = @SplitID
    
    WHILE @DealID IS NOT NULL
    BEGIN
    SET @TopID = @DealID
    
    SELECT @DealID = ParentID
    FROM dbo.Deal
    WHERE DealID = @DealID
    
    END
    
    INSERT INTO @DealTree
    SELECT
    @TopID
    , NULL
    , @CurrLvl
    FROM dbo.Deal 
    WHERE DealID = @TopID
    
    INSERT INTO @DealTree
    SELECT 
    Deal.DealID
    , Deal.ParentID
    , @CurrLvl + 1
    FROM @DealTree Tree
    INNER JOIN dbo.Deal 
    ON Tree.DealID = Deal.ParentID
    WHERE Tree.lvl = @CurrLvl
    
    SELECT @Cnt = @@ROWCOUNT
    
    SET @CurrLvl = @CurrLvl + 1
    
    WHILE @Cnt > 0
    BEGIN
    
    INSERT INTO @DealTree
    SELECT 
    Deal.DealID
    , Deal.ParentID
    , @CurrLvl + 1
    FROM @DealTree Tree
    INNER JOIN dbo.Deal 
    ON Tree.DealID = Deal.ParentID
    WHERE Tree.lvl = @CurrLvl
    
    SELECT @Cnt = @@ROWCOUNT
    
    SET @CurrLvl = @CurrLvl + 1
    
    END
    
    SELECT DealID, ParentID, Lvl
    FROM @DealTree
    
    END

    This code works ok given any of the above deal numbers it returns the full hierarchy that is related to that deal number.

    Now I find out that I have to provide the Lvl field in dotted notation so the output should look like this.

    DealID      ParentID    Lvl     
    ----------- ----------- --------
    1029        NULL        0
    1030        1029        0.0
    1031        1029        0.1
    1032        1030        0.0.0
    1033        1030        0.0.1
    1034        1030        0.0.2
    1035        1030        0.0.3
    1036        1030        0.0.4
    1037        1030        0.0.5
    1038        1032        0.0.0.0
    1039        1032        0.0.0.1
    1040        1037        0.0.5.0
    1041        1037        0.0.5.1

    Anyone got some ideas?

    --------------------
    Colt 45 - the original point and click interface

  • Try this

    BEGIN

     DECLARE @SplitID int

     SET @SplitID = 1035

     SET NOCOUNT ON

     SET DATEFORMAT dmy

     DECLARE @DealTree table (

      DealID int

      , ParentID int

      , lvl varchar(50))

     

     DECLARE @Temp table (

      DealID int

      , ParentID int)

     DECLARE @Level table (

      DealID int

      , ParentID int

      , Lvl int)

     DECLARE @TopID int

     DECLARE @DealID int

     DECLARE @cnt int

     SELECT @DealID = ParentID

     FROM dbo.Deal

     WHERE DealID = @SplitID

     WHILE @DealID IS NOT NULL

     BEGIN

      SET @TopID = @DealID

      SELECT @DealID = ParentID

      FROM dbo.Deal

      WHERE DealID = @DealID

     END

     INSERT INTO @Temp

     SELECT

      @TopID

      , NULL

     FROM dbo.Deal

     WHERE DealID = @TopID

     INSERT INTO @DealTree

     SELECT

      DealID

      , ParentID

      , '0'

     FROM @Temp

     SELECT @Cnt = 1

     WHILE @Cnt > 0

     BEGIN

      DELETE FROM @Level

      INSERT INTO @Level

      SELECT

       Deal.DealID

       , Deal.ParentID

       , 0

      FROM @Temp Tmp

      INNER JOIN dbo.Deal Deal

       ON Deal.ParentID = Tmp.DealID

      SELECT @Cnt = @@ROWCOUNT

      DELETE FROM @Temp

      INSERT INTO @Temp

      SELECT DealID, ParentID

      FROM @Level

      IF @Cnt > 0

       BEGIN

       UPDATE l

       SET l.Lvl = (SELECT COUNT(*)

         FROM @Level l2

         WHERE l2.ParentID = l.ParentID

         AND l2.DealID < l.DealID)

       FROM @Level l

       INSERT INTO @DealTree

       SELECT

        l.DealID

        , l.ParentID

        , Tree.lvl + '.' + CAST(l.Lvl as varchar)

       FROM @Level l

       INNER JOIN @DealTree Tree

        ON Tree.DealID = l.ParentID

       END

     END

     SELECT DealID, ParentID, Lvl

     FROM @DealTree

    END

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply