sql server - CTE Recursion to get tree hierarchy

Question

I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I've simplified the data for sake of example):

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

Graphical view of the tree structure (* denotes 'service'):

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

Using this query, I can get the hierarchy (just pretend 'A' is a uniqueidentifier, I know it isn't in real life):

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

This gives me the children of EstimateID 'A', but in the order that it appears in the table. ie:

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

Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:

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

So, the order that I need the results, in this example, is:

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

What do I need to add to my query to accomplish this?

Answer 1

Try this:

;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

With Path - rows a sorted by parents nodes

If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Pathcolumn....

Here SQLFiddle with sample of data