sql server - CTE Recursion to get tree hierarchy -

i need ordered hierarchy of tree, in specific way. table in question looks bit (all id fields uniqueidentifiers, i've simplified data sake of example):

estimateitemid    estimateid    parentestimateitemid     itemtype --------------    ----------    --------------------     --------        1                             null              product        2                               1               product        3                               2               service        4                             null              product        5                               4               product        6                               5               service        7                               1               service        8                               4               product

graphical view of tree structure (* denotes 'service'):

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

using query, can hierarchy (just pretend 'a' uniqueidentifier, know isn't in real life):

declare @estimateid uniqueidentifier select @estimateid = 'a'  ;with temp as(     select * estimateitem     estimateid = @estimateid      union      select ei.* estimateitem ei     inner join temp x on ei.parentestimateitemid = x.estimateitemid )  select * temp 

this gives me children of estimateid 'a', in order appears in table. ie:

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

unfortunately, need ordered hierarchy result set follows following constraints:

 1. each branch must grouped 2. records itemtype 'product' , parent top node  3. records itemtype 'product' , non-null parent grouped after top node  4. records itemtype 'service' bottom node of branch 

so, order need results, in example, is:

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

what need add query accomplish this?

try this:

;with items (     select estimateitemid, itemtype     , 0 level     , cast(estimateitemid varchar(255)) path     estimateitem      parentestimateitemid null , estimateid = @estimateid      union      select i.estimateitemid, i.itemtype     , level + 1     , cast(path + '.' + cast(i.estimateitemid varchar(255)) varchar(255))     estimateitem     inner join items itms on itms.estimateitemid = i.parentestimateitemid )  select * items order path 

with path - rows sorted parents nodes

if want sort childnodes itemtype each level, can play level , substring of pathcolumn....

here sqlfiddle sample of data