Given a hierarchical table like this:
CREATE TABLE [dbo].[btree]
(
id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
I would like to obtain the whole tree structure.
For instance, using this data:
INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2, 1, '1.1 Group');
INSERT INTO [btree] VALUES (3, 1, '1.2 Group');
INSERT INTO [btree] VALUES (4, 2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5, 2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6, 3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7, 3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8, 4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9, 4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10, 5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11, 5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12, 6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13, 6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14, 7, '1.2.2.1 Items');
I would like to obtain:
+----+-----------+---------------------+
| id | parent_id | description |
+----+-----------+---------------------+
| 1 | NULL | 1 Root |
| 2 | 1 | 1.1 Group |
| 4 | 2 | 1.1.1 Group |
| 8 | 4 | 1.1.1.1 Items |
| 9 | 4 | 1.1.1.2 Items |
| 5 | 2 | 1.1.2 Group |
| 10 | 5 | 1.1.2.1 Items |
| 11 | 5 | 1.1.2.2 Items |
| 3 | 1 | 1.2 Group |
| 6 | 3 | 1.2.1 Group |
| 12 | 6 | 1.2.1.1 Items |
| 13 | 6 | 1.2.1.2 Items |
| 7 | 3 | 1.2.2 Group |
| 14 | 7 | 1.2.2.1 Items |
+----+-----------+---------------------+
I'm fetching records using a recursive query like this:
;WITH tree AS
(
SELECT c1.id, c1.parent_id, c1.name, [level] = 1
FROM dbo.[btree] c1
WHERE c1.parent_id IS NULL
UNION ALL
SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE(' ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;
And this is the current result:
+----+-----------+---------------------+
| id | parent_id | description |
| 1 | NULL | 1 Root |
| 2 | 1 | 1.1 Group |
| 3 | 1 | 1.2 Group |
| 6 | 3 | 1.2.1 Group |
| 7 | 3 | 1.2.2 Group |
| 14 | 7 | 1.2.2.1 Items |
| 12 | 6 | 1.2.1.1 Items |
| 13 | 6 | 1.2.1.2 Items |
| 4 | 2 | 1.1.1 Group |
| 5 | 2 | 1.1.2 Group |
| 10 | 5 | 1.1.2.1 Items |
| 11 | 5 | 1.1.1.2 Items |
| 8 | 4 | 1.1.1.1 Items |
| 9 | 4 | 1.1.1.2 Items |
+----+-----------+---------------------+
I can't figure out how to order it by levels.
Is there a way to set a rank for each sub-level?
I've set up a Rextester
Best Answer
Add a "path" field and sort by that similar to a file path. As ypercube mentioned, the sorting is overly simplistic in this example and just happens to work but for simplicity's sake I'll leave as is. Most of the time when I use this pattern I sort by name rather than ID anyway.
Here a rextester