CTE per ottenere tutti i figli (discendenti) di un genitore

Ho questo problema che mi sta dando un mal di testa …

Supponiamo, ho una tabella con alcune migliaia di righe e la struttura della tabella è costituita da una relazione genitore -> figlio.

Le relazioni possono arrivare fino a 6 livelli. Ecco un esempio della struttura della tabella:

ProductId | ParentId | Levels 1174 0 1174 311 1174 311, 1174 1186 311 1186, 311, 1174 448 1186 448, 1186, 311, 1174 3365 448 3365, 448, 1186, 311, 1174 

Abbiamo un processo che scorre attraverso l’intera tabella per ottenere le relazioni e salva la colonna “livelli”, questo processo è molto lento (a causa dei loop) e ho provato con qualche cte a ottenere le relazioni ma fallito miseramente.

Finora ho provato questo cte ma non fa quello che speravo e anche, sembra replicare le righe …

 ;With Parents(ProductId, ParentId, Levels) As( Select ProductId, ParentId, Levels From Products Where ParentId = 0 Union All Select p.ProductId, p.ParentId, p.Levels From Products p Join Parents cte On cte.ProductId = p.ParentId ) Select * From Parents 

Come ho già detto, abbiamo un processo che fa girare il tavolo, fa il suo lavoro ma può richiedere fino a 30 minuti, la mia domanda è che c’è un modo migliore per farlo? so che il CTE mi permette di farlo, ma io lo succhio, inoltre, la colonna dei livelli dovrebbe essere calcasting e aggiornata sul tavolo, è ansible?

Ecco un Sqlfiddle nel caso qualcuno possa aiutare, grazie!

Questo dovrebbe farlo:

 WITH MyTest as ( SELECT P.ProductID, P.ParentID, CAST(P.ProductID AS VarChar(Max)) as Level FROM Products P WHERE P.ParentID = 0 UNION ALL SELECT P1.ProductID, P1.ParentID, CAST(P1.ProductID AS VarChar(Max)) + ', ' + M.Level FROM Products P1 INNER JOIN MyTest M ON M.ProductID = P1.ParentID ) SELECT * From MyTest 

Ed ecco l’aggiornato SQL Fiddle .

Inoltre, dai un’occhiata a questo link per aiuto con CTE … Sono sicuramente bene sapere:

Spero che questo faccia il trucco!

 ;With Parents(ProductId, ParentId, Level, levels) As( Select ProductId, ParentId, 0, cast(ltrim(str(productId,8,0)) as varchar(max)) From Products Where ParentId = 0 Union All Select p.ProductId, p.ParentId, par.Level + 1, cast( levels + ', ' + ltrim(str(productId,8,0)) as varchar(max)) From Products p Join Parents par On par.ProductId = p.ParentId ) Select * From Parents Order By Level