Posso obter uma estrutura de árvore de uma tabela autorreferenciada (hierárquica)?

Adicione um campo "caminho" e classifique por semelhante a um caminho de arquivo. Como o ypercube mencionou, a classificação é excessivamente simplista neste exemplo e simplesmente funciona, mas, para simplificar, deixarei como está. Na maioria das vezes, quando uso esse padrão, classifico por nome em vez de ID.

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)
;

aqui um rextester

Trapaça, só um pouquinho ;) Olha ma, sem recursão!

Testado em 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 ;

Claro que o acima é bastante limitado. Ele funciona apenas sob as suposições:

• a namecoluna armazenou (na primeira parte) o "caminho" real.
• a profundidade da árvore é no máximo 4 (portanto, o caminho tem até 4 partes).
• o CAST .. AS inté necessário apenas se as partes forem números.

Explicação: O código funciona usando a função PARSENAME()que tem como objetivo principal dividir um nome de objeto em suas 4 partes:

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

Note que a ordem é inversa. Como exemplo, PARSENAME('dbo.btree', 2)nos dará 'dbo'como resultado. Com 3, obteremos NULL (é por isso que o REVERSE()é usado duas vezes no código. Caso contrário, obteríamos os nulos no início. O '1.2'seria analisado null, null, 1, 2enquanto queremos 1, 2, null, null. )

Conclusão: depois de tudo isso, devo acrescentar que a resposta de Bob Campbel é o caminho a seguir, pois é mais geral e produz (na coluna "path" no resultado) a hierarquia do caminho, que pode ser usada para o arquivo ORDER BY.

Outras opções que você pode considerar - se o tamanho da tabela aumentar e a solução recursiva se tornar lenta - é armazenar o caminho em uma coluna separada (em um formato bom para ordenação, ou seja, com preenchimento) ou usar o fornecido HierarchyIDtipo que é exatamente para este caso de uso, dados hierárquicos.