hierar, Sql Server

Language: Layout:

IF OBJECT_ID('[dbo].[btree2]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[btree2];
GO

CREATE TABLE [dbo].[btree2]
(
  id INT PRIMARY KEY
, sub_of INT
, name NVARCHAR(20)
);
GO

INSERT INTO [btree2] VALUES (1,    0, 'Level 1A');
INSERT INTO [btree2] VALUES (2,    0, 'Level 1B');
INSERT INTO [btree2] VALUES (3,    0, 'Level 1C');
INSERT INTO [btree2] VALUES (4,    0, 'Level 1D');
INSERT INTO [btree2] VALUES (5,    1, 'Level 2A');
INSERT INTO [btree2] VALUES (6,    2, 'Level 2B');
INSERT INTO [btree2] VALUES (7,    3, 'Level 2C');
INSERT INTO [btree2] VALUES (8,    7, 'Level 3C');
INSERT INTO [btree2] VALUES (9,    8, 'Level 4C');
INSERT INTO [btree2] VALUES (10,   9, 'Level 5C');
INSERT INTO [btree2] VALUES (11,   6, 'Level 3B');

;WITH tree AS
(
    SELECT c1.id, c1.name, c1.sub_of, [id_level] = c1.id, [level] = 1
    FROM dbo.[btree2] c1
    WHERE c1.sub_of = 0
    UNION ALL
    SELECT c2.id, c2.name, c2.sub_of, [id_level] = tree.[id_level], [level] = tree.[level] + 1
    FROM dbo.[btree2] c2 INNER JOIN tree ON c2.sub_of = tree.id
)
SELECT t1.id, t1.sub_of, t1.name, t1.id_level, t1.level
FROM tree t1
     INNER JOIN (SELECT id_level, MAX(level) AS level FROM tree GROUP BY id_level) t2
     ON t1.id_level = t2.id_level AND t1.level = t2.level
ORDER BY id_level, level
OPTION (MAXRECURSION 0)
;

IF OBJECT_ID('[dbo].[btree2]', 'U') IS NOT NULL 
    DROP TABLE [dbo].[btree2];
GO

View schema
Execution time: 0,05 sec, rows selected: 4, rows affected: 11, absolute service time: 0,17 sec