SQL query to get multihierarchy items

Question

in my SQL Table i have following data

ID  Level   Description     Code    MasterID
1   1       Cars            AD0       NULL
2   1       Trucks          JF1       NULL
3   1       Items           YU2       NULL
4   2       New Cars        AS3        1
5   2       Used Cars       TG4        1
6   2       Car parts       UJ5        1
7   2       New trucks      OL6        2
8   2       Used trucks     PL7        2
9   2       Truck parts     KJL8       2
10  2       Factory stuff   UY9        3
11  2       Custom stuff    RT10       3
12  3       Toyota          6YH11      4
13  3       BMW             9OKH12     4
14  3       VW              13         5
15  3       Tiers Type I    J14        6
16  3       Tiers Type II   J15        6
17  3       Tiers Type III  ADS16      9
18  3       Seats           SA17       6
19  3       Doors           UU18       6
20  3       Lights          9OL19      6
21  4       Left light      GH20       20
22  4       Right light     H21        20
23  4       Left door       JHJ22      19
24  4       Michelin        UY23       16
25  4       Vredestein      GTF24      17
26  4       Dunlop          25         15

My achievement is to get all hierarchy data for each single item. For Exmaple, the outpu should look like as following

ID  Level   Description   Code   MasterId1   Description1     MasterId2   Description2    MasterId3    Description3

24   4       Michelin     UY23       16      Tiers Type II       6         Car Parts           1        Cars
.
.
19   3       Doors        UU18       6       Car Parts           1          Cars               NULL      NULL
.
.
10   2       Factory Stuff UY9       3       Items               NULL     NULL                 NULL     NULL
.
.
3    1        Items        NULL      NULL     NULL               NULL     NULL                 NULL     NULL
.
.

If somebody can help or give an advise how to achieve this?

Answer 1

This is not dynamic but it could be pretty easily.

Using a recursive cte you can get the hierarchy for the entire table and self join a few times to get the table structure you want.

;WITH cte AS 
(
    SELECT *, ID AS [RootID], 1 AS [MasterLevel] FROM Table1
    UNION ALL
    SELECT t1.*, cte.[RootID], cte.[MasterLevel] + 1  FROM Table1 t1
    JOIN cte ON t1.ID = cte.MasterID  
)
SELECT  r.ID, r.[Level], r.[Description], r.[Code],
        m1.ID AS MasterId1, m1.[Description] AS Description1, 
        m2.ID AS MasterId2, m1.[Description] AS Description2,
        m3.ID AS MasterId3, m1.[Description] AS Description3
FROM    cte r
        LEFT JOIN cte m1 ON m1.[RootID] = r.[RootID] AND m1.MasterLevel = 2
        LEFT JOIN cte m2 ON m2.[RootID] = r.[RootID] AND m2.MasterLevel = 3
        LEFT JOIN cte m3 ON m3.[RootID] = r.[RootID] AND m3.MasterLevel = 4
WHERE   r.MasterLevel = 1
ORDER BY r.RootID DESC, r.MasterLevel

This would build a dynamic sql to get master and desciption fields based on the maximum Level value. or you could define how many levels you want to see by changing the @MaxLevel

DECLARE @Sql VARCHAR(MAX) = '',
        @SelectSql VARCHAR(MAX) = '',
        @JoinSql VARCHAR(MAX) = '',
        @MaxLevel INT,
        @idx INT = 1

SET @MaxLevel = (SELECT MAX([Level]) FROM Table1)
WHILE @idx < @MaxLevel
BEGIN
    SET @SelectSql = @SelectSql + REPLACE(', m<index>.ID AS MasterId<index>, m<index>.[Description] AS Description<index> ', '<index>', @idx)
    SET @JoinSql = @JoinSql + REPLACE(' LEFT JOIN cte m<index> ON m<index>.[RootID] = r.[RootID] AND m<index>.MasterLevel = <index> ', '<index>', @idx)
    SET @idx = @idx + 1
END        
SET @Sql = '
    ;WITH cte AS 
    (
        SELECT *, ID AS [RootID], 0 AS [MasterLevel] FROM Table1
        UNION ALL
        SELECT t1.*, cte.[RootID], cte.[MasterLevel] + 1  FROM Table1 t1
        JOIN cte ON t1.ID = cte.MasterID  
    )
    SELECT  r.ID, r.[Level], r.[Description], r.[Code]' + @SelectSql
    + 'FROM cte r ' + @JoinSql
    + 'WHERE    r.MasterLevel = 0
    ORDER BY r.RootID DESC, r.MasterLevel'

EXEC(@Sql)