CTE para obtener todos los hijos (descendientes) de un padre

Tengo este problema que me está dando dolor de cabeza …

Digamos que tengo una tabla con miles de filas y la estructura de la tabla consiste en una relación padre -> hijo.

Las relaciones pueden ir hasta 6 niveles. Aquí hay una muestra de la estructura de la tabla:

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 

Obtuvimos un proceso que recorre toda la tabla para obtener las relaciones y guarda la columna de “niveles”, este proceso es muy lento (debido a los bucles) y lo he intentado con algo de cte para obtener las relaciones, pero fallé miserabily.

Hasta ahora he intentado esta cte pero no hace lo que esperaba y, además, parece estar replicando filas …

 ;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 

Como mencioné antes, obtuvimos un proceso que hace un bucle en la mesa, hace su trabajo pero puede llevar hasta 30 minutos, mi pregunta es ¿hay una mejor manera de hacerlo? Sé que CTE me permite hacerlo, pero lo apeo, también, la columna de niveles debe calcularse y actualizarse en la mesa, ¿es posible?

Aquí hay un Sqlfiddle en caso de que alguien pueda ayudar, ¡gracias!

Esto debería hacerlo:

 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 

Y aquí está el SQL Fiddle actualizado.

Además, consulte este enlace para obtener ayuda con los CTE … Definitivamente son buenos para saber:

Espero que este haga el truco!

 ;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