Recursão CTE para obter hierarquia de tree

Eu preciso obter uma hierarquia ordenada de uma tree, de uma maneira específica. A tabela em questão parece um pouco com isso (todos os campos de ID são uniqueidentifiers, simplifiquei os dados para fins de exemplo):

  EstimateItemID EstimateID ParentEstimateItemID ItemType
 -------------- ---------- -------------------- ------ -
        1 produto A NULL
        2 A 1 produto
        3 serviço A 2
        4 Um produto NULL
        5 um produto 4
        6 um serviço 5
        7 A 1 serviço
        8 um produto 4 

Vista gráfica da estrutura da tree (* denota ‘serviço’):

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

Usando essa consulta, posso obter a hierarquia (apenas finja que ‘A’ é um identificador único, sei que não é na 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 

Isso me dá os filhos de EstimateID ‘A’, mas na ordem em que aparecem na tabela. ie:

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

Infelizmente, o que preciso é de uma hierarquia ordenada com um conjunto de resultados que segue as seguintes restrições:

 1. cada ramo deve ser agrupado
 2. registros com ItemType 'produto' e pai são o nó superior 
 3. registros com ItemType 'product' e pai não-NULL agrupados após o nó superior 
 4. registros com ItemType 'service' são o nó inferior de um branch

Então, a ordem que eu preciso dos resultados, neste exemplo, é:

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

O que preciso adicionar à minha consulta para realizar isso?

Tente isto:

 ;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 

Com Path – linhas ordenadas por nós pais

Se você quiser ordenar childnodes por ItemType para cada nível, então você pode jogar com Level e SUBSTRING da coluna Path ….

Aqui SQLFiddle com amostra de dados

Este é um complemento da ótima ideia do Fabio. Como eu disse na minha resposta ao post original dele. Re-publiquei a ideia dele usando dados mais comuns, nome da tabela e campos para facilitar o acompanhamento de outras pessoas.

Obrigado fabio! Grande nome pelo caminho.

Primeiro alguns dados para trabalhar com:

 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'); 

Agora a 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; 

Eu acredito que você precisa adicionar o seguinte aos resultados do seu CTE …

  1. BranchID = algum tipo de identificador que identifica exclusivamente o ramo. Perdoe-me por não ser mais específico, mas não tenho certeza do que identifica um ramo para suas necessidades. Seu exemplo mostra uma tree binária na qual todos os ramos retornam à raiz.
  2. ItemTypeID onde (por exemplo) 0 = Produto e 1 = serviço.
  3. Pai = identifica o pai.

Se eles existirem na saída, acho que você deve poder usar a saída de sua consulta como outra CTE ou como a cláusula FROM em uma consulta. Ordenar por BranchID, ItemTypeID, Pai.