sql server - recursivo - Recursión CTE para obtener jerarquía de árbol

sql table with

Question

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 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 del árbol (* denota ''servicio''):

A ___/ /___ / / 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. 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?

Answer 1

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 rama. 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 ramas 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.

Answer 2

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;

Answer 3

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