Recursión CTE para obtener jerarquía de árbol

Necesito obtener una jerarquía ordenada de un árbol, de una manera específica. La tabla en cuestión se parece un poco a esto (todos los campos ID son uniqueidentifiers, simplifiqué los datos por ejemplo):

 EstimateItemID EstimateID ParentEstimateItemID ItemType -------------- ---------- -------------------- - ------ 1 A NULL producto 2 A 1 producto 3 A 2 servicio 4 A NULL producto 5 A 4 producto 6 A 5 servicio 7 A 1 servicio 8 A 4 producto 

Vista gráfica de la estructura del árbol (* denota ‘servicio’):

            UN
        ___ / \ ___
       / \
     1 4
    / \ / \
   2 7 * 5 8
  / /
 3 * 6 *

Al usar esta consulta, puedo obtener la jerarquía (solo pretender que ‘A’ es un identificador único, sé que no es en la vida real):

DECLARE @EstimateID uniqueidentifier SELECT @EstimateID = 'A' ;WITH temp as( SELECT * FROM EstimateItem WHERE EstimateID = @EstimateID UNION ALL SELECT ei.* FROM EstimateItem ei INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID ) SELECT * FROM temp 

Esto me da los hijos de EstimateID ‘A’, pero en el orden en que aparece en la tabla. es decir:

  EstimateItemID
 --------------
       1
       2
       3
       4
       5
       6
       7
       8 

Desafortunadamente, lo que necesito es una jerarquía ordenada con un conjunto de resultados que siga las siguientes restricciones:

 1. cada twig debe estar agrupada
 2. registros con ItemType 'producto' y parent son el nodo superior 
 3. registros con ItemType 'producto' y no padre NULL agrupados después del nodo superior 
 4. los registros con ItemType 'servicio' son el nodo inferior de una twig

Entonces, el orden en el que necesito los resultados, en este ejemplo, es:

  EstimateItemID
 --------------
       1
       2
       3
       7
       4
       5
       8
       6

¿Qué necesito agregar a mi consulta para lograr esto?

Prueba esto:

 ;WITH items AS ( SELECT EstimateItemID, ItemType , 0 AS Level , CAST(EstimateItemID AS VARCHAR(255)) AS Path FROM EstimateItem WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID UNION ALL SELECT i.EstimateItemID, i.ItemType , Level + 1 , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255)) FROM EstimateItem i INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID ) SELECT * FROM items ORDER BY Path 

Con Path – rows a ordenado por nodos de padres

Si desea clasificar los tipos de niños por tipo de ItemType para cada nivel, entonces puede jugar con la columna Level y SUBSTRING de la Path ….

Aquí SQLFiddle con muestra de datos

Este es un complemento a la gran idea de Fabio desde arriba. Como dije en mi respuesta a su publicación original. He vuelto a publicar su idea usando datos, nombre de tabla y campos más comunes para que otros los sigan más fácilmente.

Gracias Fabio! Gran nombre por cierto.

Primero algunos datos para trabajar:

 CREATE TABLE tblLocations (ID INT IDENTITY(1,1), Code VARCHAR(1), ParentID INT, Name VARCHAR(20)); INSERT INTO tblLocations (Code, ParentID, Name) VALUES ('A', NULL, 'West'), ('A', 1, 'WA'), ('A', 2, 'Seattle'), ('A', NULL, 'East'), ('A', 4, 'NY'), ('A', 5, 'New York'), ('A', 1, 'NV'), ('A', 7, 'Las Vegas'), ('A', 2, 'Vancouver'), ('A', 4, 'FL'), ('A', 5, 'Buffalo'), ('A', 1, 'CA'), ('A', 10, 'Miami'), ('A', 12, 'Los Angeles'), ('A', 7, 'Reno'), ('A', 12, 'San Francisco'), ('A', 10, 'Orlando'), ('A', 12, 'Sacramento'); 

Ahora la consulta recursiva:

 -- Note: The 'Code' field isn't used, but you could add it to display more info. ;WITH MyCTE AS ( SELECT ID, Name, 0 AS TreeLevel, CAST(ID AS VARCHAR(255)) AS TreePath FROM tblLocations T1 WHERE ParentID IS NULL UNION ALL SELECT T2.ID, T2.Name, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath FROM tblLocations T2 INNER JOIN MyCTE itms ON itms.ID = T2.ParentID ) -- Note: The 'replicate' function is not needed. Added it to give a visual of the results. SELECT ID, Replicate('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath FROM MyCTE ORDER BY TreePath; 

Creo que debe agregar lo siguiente a los resultados de su CTE …

  1. BranchID = algún tipo de identificador que identifica de manera única la twig. Perdóname por no ser más específico, pero no estoy seguro de qué identifica una sucursal para tus necesidades. Su ejemplo muestra un árbol binario en el que todas las twigs vuelven a la raíz.
  2. ItemTypeID donde (por ejemplo) 0 = Producto y 1 = servicio.
  3. Parent = identifica al padre.

Si existen en el resultado, creo que debería poder usar el resultado de su consulta como otro CTE o como la cláusula FROM en una consulta. Ordene por BranchID, ItemTypeID, Parent.