CODEWITHSUNDEEP
sqlsql-server
Najlepszak
Najlepszak

Reputation: 55

How to display the ID's of all the leaves related to a given level along with this level

I have a tree structure:

CARS (ID = 3)
    | AUDI (ID: 5, ParentId: 3)
        | DIR1 (ID: 9, ParentId: 5)
        | DIR2 (ID: 7, ParentId: 5)
        | DIR3 (ID: 8, ParentId: 5)
    | FIAT (ID: 10, ParentId: 3)
    | FORD (ID: 11, ParentId: 3)
        | DIR4 (ID: 12, ParentId: 11)
    | RENAULT (ID: 6, ParentId: 3)
    | TOYOTA (ID: 4, ParentId: 3)

I need to display the ID's of all the leaves related to a given level along with this level as follows:

ID Name ID2 Name2
3 CARS 3
3 CARS 5 AUDI
3 CARS 9 DIR1
3 CARS 7 DIR2
3 CARS 8 DIR3
3 CARS 10 FIAT
3 CARS 11 FORD
3 CARS 12 DIR4
3 CARS 6 RENAULT
3 CARS 4 TOYOTA
5 AUDI 5
5 AUDI 9 DIR1
5 AUDI 7 DIR2
5 AUDI 8 DIR3
9 DIR1 9 DIR1
7 DIR2 7 DIR2
8 DIR3 8 DIR3
10 FIAT 10
11 FORD 11
11 FORD 12 DIR4
12 DIR4 12 DIR4
6 RENAULT 6
4 TOYOTA 4

This is a dynamic structure, users can delete and add leaves so I need a function or procedure.

I have created the following code but maybe there is a better, more elegant way to get the results:

DECLARE @T AS TABLE 
              (
                  ID int, 
                  Name varchar(255), 
                  ParentId int, 
                  Level int, 
                  Leaf int
              )

INSERT INTO @T 
VALUES (3, 'CARS', NULL, 1, 0),
       (4, 'TOYOTA', 3, 2, 1),
       (5, 'AUDI', 3, 2, 2),
       (6, 'RENAULT', 3, 2, 3),
       (7, 'DIR2', 5, 3, 1),
       (8, 'DIR3', 5, 3, 2),
       (9, 'DIR1', 5, 3, 3),
       (10, 'FIAT', 3, 2, 4),
       (11, 'FORD', 3, 2, 5),
       (12, 'DIR4', 11, 3, 1)

;WITH cte1 AS 
(
    SELECT  
        ID, Name, ParentId, Level, Leaf 
    FROM 
        @T
), cte2 AS
(        
    SELECT  
        ID, Name, ParentId, 1 AS [Level],                  
        CAST((RTRIM(LTRIM(STR(ID, 15, 0)))) AS VARCHAR(MAX)) AS Ids              
    FROM    
        @T t1
    WHERE   
        ParentId IS NULL

    UNION ALL
        
    SELECT  
        t2.ID, t2.Name, t2.ParentId, M.[level] + 1 AS [Level],                 
        CAST((M.Ids + ',' + RTRIM(LTRIM(STR(t2.ID, 15, 0)))) AS VARCHAR(MAX)) AS Ids
    FROM    
        @T AS t2
    JOIN 
        cte2 AS M ON t2.ParentId = M.ID  
)
SELECT 
    B.ID, B.Name AS Name,
    cte2.ID AS ID2, cte2.Name
FROM 
    cte2
CROSS APPLY
    (SELECT Split.a.value('.', 'VARCHAR(1024)') AS gId
     FROM
         (SELECT    
              CAST ('<M>' + REPLACE(Ids, ',', '</M><M>') + '</M>' AS XML) AS String) A
     CROSS APPLY 
         String.nodes ('/M') AS Split(a)) AS A
CROSS APPLY 
    (SELECT CAST(gId as int) AS G_ID) AS G_ID
INNER JOIN 
    @T B ON B.Id = G_ID

Upvotes: 0

Views: 55

Answers (1)

Serg
Serg

Reputation: 22811

Is it what you are after?

WITH cte AS (
    SELECT  ID, Name, ID id2, Name Name2, ParentId, 1 AS [Level] 
      , CAST(STR(ID,15,0) AS VARCHAR(MAX)) AS Ids 
    FROM    tbl
    UNION ALL
        
    SELECT  m.ID, m.Name, t2.id, t2.name,  t2.ParentId, M.[level] + 1
      , M.Ids + ',' + STR(t2.ID,15,0)
    FROM tbl AS t2
    JOIN cte AS M ON t2.ParentId = M.ID2  
)
select ID, Name, id2, Name2, level, replace(ids, ' ', '')
from cte
order by id, ids;

All paths from a node.

db<>fiddle

Upvotes: 1

Related Questions