Query Sql per tabella ad albero

Ho una tabella con struttura ad albero:

id parentId name ---------------- 1 0 Category1 2 0 Category2 3 1 Category3 4 2 Category4 5 1 Category5 6 2 Category6 7 3 Category7 

In sql query resut ho bisogno di una tabella come:

 id parentId level name ---------------------- 1 0 0 Category1 3 1 1 Category3 7 3 2 Category7 5 1 1 Category5 2 0 0 Category2 4 2 1 Category4 6 2 1 Category6 

Chi può aiutarmi a scrivere query ms-sql? Grazie!

Espandendo sulla risposta a_horse_with_no_name, questo mostra come utilizzare l’ implementazione di SQL Server ricorsiva di CTE ( cross ricorsivo a record singolo applicato ) in combinazione con row_number () per produrre l’output esatto nella domanda.

 declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,'Category1' insert @t select 2, 0, 'Category2' insert @t select 3, 1, 'Category3' insert @t select 4 , 2, 'Category4' insert @t select 5 , 1, 'Category5' insert @t select 6 , 2, 'Category6' insert @t select 7 , 3, 'Category7' ; WITH tree (id, parentid, level, name, rn) as ( SELECT id, parentid, 0 as level, name, convert(varchar(max),right(row_number() over (order by id),10)) rn FROM @t WHERE parentid = 0 UNION ALL SELECT c2.id, c2.parentid, tree.level + 1, c2.name, rn + '/' + convert(varchar(max),right(row_number() over (order by tree.id),10)) FROM @t c2 INNER JOIN tree ON tree.id = c2.parentid ) SELECT * FROM tree order by RN 

Per essere onesti, utilizzare gli ID stessi per produrre il “percorso” dell’albero funzionerebbe, dal momento che stiamo ordinando direttamente da id, ma ho pensato di inserire la funzione row_number ().

 WITH tree (id, parentid, level, name) as ( SELECT id, parentid, 0 as level, name FROM your_table WHERE parentid = 0 UNION ALL SELECT c2.id, c2.parentid, tree.level + 1, c2.name FROM your_table c2 INNER JOIN tree ON tree.id = c2.parentid ) SELECT * FROM tree 

Non ho attualmente SQL Server a portata di mano per testarlo, quindi ci potrebbero essere alcuni errori di battitura (errori di syntax) in là