declare @t table ( ID int, Parent_ID int, Name varchar(255))
insert into @t (ID, Parent_ID, Name)
select NULL, NULL, 'Something 1' union all
select 1, NULL, 'Something 1' union all
select 2, NULL, 'Something 2' union all
select 8, 3, 'Something 3' union all
select 3, 1, 'Something 3' union all
select 4, 1, 'Something 4' union all
select 5, 2, 'Something 5' union all
select 6, 2, 'Something 6' union all
select 9, 20, 'Something 6' union all -- broken branch
select 7, 6, 'Something 7'
; with c(ID, Parent_ID, newName)
as
(
select ID, Parent_ID, convert(varchar(255),STR(ISNULL(ID,0)))
from @t
where ISNULL(Parent_ID,0) = 0
union all
select t.ID, t.Parent_ID, convert(varchar(255), c.newName + ' - ' + convert(varchar(255),STR(ISNULL(t.ID,0))))
from @t t
inner join c on t.Parent_ID = c.ID
)
select * from c order by newName
-- 05/09/2008 07:13:03: post edited by sergey.