Create Leg Column in Hierarchical Data

Question

I'm trying to add a column in my Select statement that can assign the leg number to the three legs I have. The leg number is always assigned at Level 1 in no particular order.

Note: my actual data is random 6 digit IDs and not numbered 1,2,3,4...

Visual Tree

Data:

declare @mytable table(id int, parent_id int, level int)

INSERT INTO @MYTABLE VALUES(1, NULL, 0) 
INSERT INTO @MYTABLE VALUES(2, 1, 1) 
INSERT INTO @MYTABLE VALUES(3, 1, 1)
INSERT INTO @MYTABLE VALUES(4, 1, 1) 
INSERT INTO @MYTABLE VALUES(5, 2, 2)
INSERT INTO @MYTABLE VALUES(6, 2, 2) 
INSERT INTO @MYTABLE VALUES(7, 4, 2)
INSERT INTO @MYTABLE VALUES(8, 4, 2) 
INSERT INTO @MYTABLE VALUES(9, 4, 2)
INSERT INTO @MYTABLE VALUES(10, 4, 2) 
INSERT INTO @MYTABLE VALUES(11, 6, 3)
INSERT INTO @MYTABLE VALUES(12, 6, 3) 
INSERT INTO @MYTABLE VALUES(13, 12, 4)
INSERT INTO @MYTABLE VALUES(14, 12, 4) 
INSERT INTO @MYTABLE VALUES(15, 12, 4)
INSERT INTO @MYTABLE VALUES(16, 10, 3) 
INSERT INTO @MYTABLE VALUES(17, 10, 3)
INSERT INTO @MYTABLE VALUES(18, 10, 3) 
INSERT INTO @MYTABLE VALUES(19, 13, 5)
INSERT INTO @MYTABLE VALUES(20, 13, 5) 
INSERT INTO @MYTABLE VALUES(21, 13, 5)
INSERT INTO @MYTABLE VALUES(22, 17, 4) 
INSERT INTO @MYTABLE VALUES(23, 17, 4)
;
    select 
        b.id, 
        b.parent_id,
    b.level
      
    from @mytable b

Desired Result

I suggest looking into CTEs and recursion, make an attempt, then show us where you are stuck. — StoneGiant
– StoneGiant, Commented Mar 26, 2024 at 13:56

Mike Petri · Accepted Answer · 2024-03-29 23:35:54Z

0

Nice challenge. Here you go!

WITH cte AS
(
    SELECT  m.id
            ,m.parent_id
            ,0 AS Lev
            ,CAST(NULL AS BIGINT) AS Leg
    FROM    @mytable AS m
    WHERE   m.parent_id IS NULL
    UNION ALL
    SELECT  cc.id
            ,cc.parent_id
            ,c.Lev + 1
            ,CASE
                      WHEN c.Lev + 1 = 1 THEN ROW_NUMBER() OVER (ORDER BY c.Lev + 1)
                      ELSE c.Leg
                  END AS Leg
    FROM    cte AS c
            INNER JOIN @mytable AS cc ON cc.parent_id = c.id
)
SELECT  cte.id
        ,cte.parent_id
        ,cte.Lev
        ,cte.Leg
FROM    cte
ORDER BY cte.id

answered Mar 29, 2024 at 23:35

Mike Petri

6054 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Sheffield Over a year ago

I appreciate your solve! But I think this only works with numbered data like I have shown. I should have given more realistic data. The b.id column actually uses random IDs (ex: 25643, 43426) instead of 1,2,3,4 etc... With that, the results don't turn out right. I see the logic you have used though and I am trying to use that think this out some more.

Collectives™ on Stack Overflow

Create Leg Column in Hierarchical Data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related