order hierarchical data into a tree using ROW_NUMBER or does it have to be done with a loop?

Question

I have a set of hierarchical data that I'm hoping to get into the right format without using a loop. Maybe it's a long day and I just don't see how to do it.

When it starts the data looks like this: (row = row ID, par = parent row ID, lev = level, has_child is obvious, had to do that or edit the space between all those numbers!)

row par lev has_child
1   NUL 0 1
2   1 1 1
3   1 1 1
4   1 1 1
5   1 1 1
6   1 1 0
148 2 2 0
149 2 2 1
145 3 2 0
146 3 2 1
9   4 2 0
11  4 2 0
12  4 2 0
13  4 2 0
14  4 2 0
15  4 2 0
16  4 2 0
17  4 2 0

I'd like it to be in a parent, child, child, child, ... order. So that all the children of a parent are shown before the next parent is started. Like this:

Parent
  |
   --- child L1
       | 
       ---- child L2
       | 
       ----- child L2 
           |
           ---- child L3 
       |
       ----- child L2 
  | 
  ---- child L1

It seems like I should be able to accomplish this using ROW_NUMBER(), but I've been messing with it for a good half hour with no luck. Do I have to do this in a loop?

Answer 1

I have an example on this question (which is more complicated, but you'll get the idea)

http://stackoverflow.com/questions/1124677/sql-server-tree-hierarchy-and-nested-sets-with-duplicate-record-ids/1124876#1124876

Answer 2

You can't with ROW_NUMBER(). You need to use a recursive CTE and create a synthetic sort order by composing the full parent path:

declare @table table (row int, par int, lev int, has_child bit);

insert into @table 
select 1, NULL, 0, 1
union all select 2,   1,   1,       1
union all select 3,   1,   1,       1
union all select 4,   1,   1,       1
union all select 5,   1,   1,       1
union all select 6,   1,   1,       0
union all select 148, 2,   2,       0
union all select 149, 2,   2,       1
union all select 145, 3,   2,       0
union all select 146, 3,   2,       1
union all select 9,   4,   2,       0
union all select 11,  4,   2,       0
union all select 12,  4,   2,       0
union all select 13,  4,   2,       0
union all select 14,  4,   2,       0
union all select 15,  4,   2,       0
union all select 16,  4,   2,       0
union all select 17,  4,   2,       0;

with cte_anchor as (
  select row, par, 0 as lev, cast(row as varchar(max)) as wbs
     from @table
     where par is null)
, cte_recursive as (
  select row, par, lev, wbs
  from cte_anchor
  union all
  select  t.row, t.par, r.lev+1 as lev
    , r.wbs + '.' + cast(t.row as varchar(max)) as wbs
  from @table t
  join cte_recursive r on t.par = r.row)
select * from cte_recursive
order by wbs