Recursión de 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 ve un poco como esto (todos los campos de ID son uniqueidentifiers, he simplificado los datos por ejemplo): 

EstimateItemID    EstimateID    ParentEstimateItemID     ItemType
--------------    ----------    --------------------     --------
       1              A                NULL              product
       2              A                  1               product
       3              A                  2               service
       4              A                NULL              product
       5              A                  4               product
       6              A                  5               service
       7              A                  1               service
       8              A                  4               product

Vista gráfica de la estructura de árbol (*denota 'servicio'): 

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

Usando esta consulta, puedo obtener la jerarquía (solo finja que 'A' es un identificadorúnico, sé que no lo 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 que aparece en la tabla. ie: 

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. each branch must be grouped
2. records with ItemType 'product' and parent are the top node 
3. records with ItemType 'product' and non-NULL parent grouped after top node 
4. records with ItemType 'service' are bottom node of a branch

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?

Author: Woods8460, 2013-08-07
Source

3 answers

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 - filas a ordenadas por nodos primarios

Si quieres ordenar childnodes por ItemType para cada nivel, entonces puedes jugar con Level y SUBSTRING de la columna Path....

Aquí SQLFiddle con una muestra de datos

 60
Author: Fabio,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-08 04:55:14

Este es un complemento a la gran idea de Fabio desde arriba. Como dije en mi respuesta a su post original. He vuelto a publicar su idea usando datos más comunes, nombre de tabla y campos para que sea más fácil para otros seguirlo.

Gracias Fabio! Gran nombre por cierto.

Primero algunos datos para trabajar con: 

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;
 5
Author: ptownbro,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-30 07:52:16

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

  1. BranchID = algún tipo de identificador que identifica de forma única la rama. Perdóneme por no ser más específico, pero no estoy seguro de qué identifica una sucursal para sus necesidades. El ejemplo muestra un árbol binario en el que todas las ramas fluyen de vuelta a la raíz.
  2. ItemTypeID donde (por ejemplo) 0 = Producto y 1 = servicio.
  3. Padre = identifica al padre.

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

 0
Author: DeadZone,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-07 15:35:18