Sort parent-child hierarchy when having previous and next sibling

Transact-SQL
1

Dear colleagues,
T-SQL is not my expertise field so I'm struggling with a SQL query.
I have a table defined in SQL Server 2008 as follows:

forum.jpg840×612 119 KB

When ParentId = -1 then member has not a parent
When PrevSiblingID = -1 then member is the first child
When NextSiblingID = -1 then member is the last child
I would like to have a recursive query which provides me the hierarchy sorted taking into consideration the order defined by next and previous sibling.

Thanks in advance

2

Sorry there was a typo:
parentId for A,B, and C is 0.
parentid for Z is -1
Thanks

3

declare @table table(itemid int, label varchar(5), parentid int, prevsilblingid int, nextsilblingid int)
insert into @table
select
10,'B',-1,50,20 union all select
20,'C',-1,10,-1 union all select
30,'B1',10,-1,40 union all select
40,'B2',10,30,-1 union all select
50,'A',-1,-1,10 union all select
60,'A1',50,-1,70 union all select
70,'A2',50,60,-1 union all select
80,'A21',70,-1,-1 union all select
90,'C1',20,-1,-1 union all select
100,'Z',0,-1,-1

;with cte as(
select itemid, parentid, label = label, path = cast(label as varchar(max))
from @table
where parentid = 0
union all
select a.itemid, a.parentid, a.label, b.path + '.' + a.label
from @table a join cte b
on case when a.parentid = -1 then 100 else a.parentid end = b.itemid
)
select row_number() over (order by (select 1)), *
from cte
order by 5

1 Like
4

shouldn't it be
parentId for A,B, and C is 100.
parentid for Z is -1

5

Hi,,you are right.

To be more concise I could even add two columns with first and last child id.

6

Hi,

i gave a try.

that query does not sort the result as expected.

Thanks!

7

I change a little the source so that we have the item A99 as first object (prevSiblingID =-1). In this way , we can test the output is in order of prevSiblingID

DECLARE @tvH TABLE
(itemID INT,
 label VARCHAR(50),
 parentID INT,
 prevSiblingID INT,
 nextSiblingID INT)

INSERT INTO @tvH(itemID,label,parentID,prevSiblingID,nextSiblingID)
VALUES(10,'B',-1,50,20),
      (20,'C', -1, 10, -1),
      (30, 'B1', 10 ,-1 ,40),
      (40, 'B2', 10, 30, -1),
      (50, 'A', -1 ,-1 ,10),
      /*(60, 'A1', 50, -1, 70),
      (70, 'A2', 50, 60, -1),
      (80, 'A21', 70, -1, -1),*/
      (60, 'A99', 50, -1, 70),
      (70, 'A1', 50, 60, -1),
      (80, 'A12', 70, -1, -1),
      (90, 'C1', 20, -1, -1),
      (100, 'Z', 0, -1, -1)


--SELECT * FROM @tvH
;WITH cteR
AS (
        SELECT itemID,label,-1 AS ParentID, label as Path
            , prevSiblingID,nextSiblingID 
        FROM @tvh 
        WHERE parentID = 0
        UNION ALL
        SELECT T.itemID,T.label,R.itemID, CAST(R.Path + '.'+ T.label AS VARCHAR(50)) AS Path 
            , T.prevSiblingID,T.nextSiblingID
        FROM (SELECT itemID,label,ParentID, label as Path, prevSiblingID,nextSiblingID FROM @tvh WHERE parentID = 0 ) AS R
            CROSS JOIN (SELECT itemID,label,ParentID , prevSiblingID,nextSiblingID FROM @tvh WHERE parentID = -1 ) AS T
        
    )
--SELECT * fROM cteR    
,cteR2
AS(
      SELECT itemID,label,ParentID, CAST(Path AS VARCHAR(50)) AS Path, prevSiblingID,nextSiblingID 
        ,CAST(Path AS VARCHAR(50)) AS PathParent
      FROM cteR
      UNION ALL
      SELECT T.itemID,T.label,T.ParentID, CAST(R.Path + '.'+ T.label AS VARCHAR(50)) AS Path, T.prevSiblingID,T.nextSiblingID 
        , CAST(R.Path AS VARCHAR(50))
      FROM @tvh AS T
      INNER JOIN cteR2 AS R
      ON R.itemID = T.parentID
)
,cteR3
AS (SELECT * 
        ,ROW_NUMBER()OVER(PARTITION BY ParentID ORDER BY prevSiblingID ASC) AS rn_Asc
        ,ROW_NUMBER()OVER(PARTITION BY ParentID ORDER BY nextSiblingID ASC) AS rn_Desc
        ,LEN(Path)-LEN(REPLACE(Path,'.','')) AS lvl
    FROM cteR2)

SELECT 
    itemID,label,ParentID,Path
    --*
FROM cteR3
ORDER BY PathParent,lvl,rn_Asc,rn_Desc--,lvl

The output:

itemID      label                                              ParentID    Path
100         Z                                                  -1          Z
50          A                                                  100         Z.A
60          A99                                                50          Z.A.A99
70          A1                                                 50          Z.A.A1
80          A12                                                70          Z.A.A1.A12
10          B                                                  100         Z.B
30          B1                                                 10          Z.B.B1
40          B2                                                 10          Z.B.B2
20          C                                                  100         Z.C
90          C1                                                 20          Z.C.C1
8

Hi,

thanks for response.

I will give a try and share my feedback.

Thanks

9

@franciscoamores,

I have to ask... why do you want previous/next sibling to be stored in separate columns and is it actually essential to use "Z", "A", etc for your hierarchical path? The reason I'm asking about the siblings is because I think they're unnecessary and looking for a reason why you think you need them. The reason why I'm asking about the hierarchical path is because having them lettered in such a fashion is going to be a continuous pain for you to maintain.

And, to be totally honest, that -1 thing is a huge pain when it comes to properly constraining this Adjacency List. It's one of the places where a NULL would be much better suited.

IF we can do away with the prev/next sibling columns and the funcky lettering in the hierarchical path, then I can show you some things that might just make your day.