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