Can I get a tree-structure from a self-referenced (hierarchical) table?

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.

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

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
GO

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.2.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');

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast('root' as varchar(100))
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1, 
           Path = Cast(tree.path+'/'+right('000000000' + cast(c2.id as varchar(10)),10) as varchar(100))
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
Order by path
OPTION (MAXRECURSION 0)
;

Here a rextester

Cheating, just a bit ;) Look ma, no recursion!

Tested at rextester.com

SELECT btree.*        -- , a,b,c,d     -- uncomment to see the parts
FROM btree 
  OUTER APPLY
    ( SELECT rlc = REVERSE(LEFT(name, CHARINDEX(' ', name)-1))) AS r
  OUTER APPLY
    ( SELECT a = CAST(REVERSE(PARSENAME(r.rlc, 1)) AS int),
             b = CAST(REVERSE(PARSENAME(r.rlc, 2)) AS int),
             c = CAST(REVERSE(PARSENAME(r.rlc, 3)) AS int),
             d = CAST(REVERSE(PARSENAME(r.rlc, 4)) AS int)
    ) AS p 
ORDER BY a, b, c, d ;

Of course the above is rather limited. It works only under the assumptions:

• the name column has stored (in the first part) the actual "path".
• the depth of the tree is maximum 4 (so the path has up to 4 parts).
• the CAST .. AS int is needed only if the parts are numbers.

Explanation: The code works by using the function PARSENAME() which has the main purpose of splitting an object name into its 4 parts:

Server.Database.Schema.Object
  |        |       |      |
 4th      3rd     2nd    1st

Note that the order is reverse. As an example, PARSENAME('dbo.btree', 2) will give us 'dbo' as a result.With 3, we'll get NULL (that's why the REVERSE() is used twice in the code. Otherwise we'd get the nulls in the beginning. The '1.2' would be parsed into null, null, 1, 2 while we want 1, 2, null, null.)

---

Conclusion: after all that, I should add that the answer by Bob Campbel is the way to go as it is more general and produces (in the "path" column in the result) the path hierarchy, which can then be used for the ORDER BY.

Other options you may consider - if the size of the table grows big and the recursive solution becomes slow - is to actually store the path in a separate column (in a format that is good for ordering, i.e. with padding) or to use the provided HierarchyID type which is exactly for this use case, hierarchical data.