quote:

---

Well I'll be darned!!

@nodes is a reference to a distinct list of nodes. In a multiparent environment, node definition must exist in a table seperate from node relationships. In a single parent environment, the relationship can be located on the node definition record.

Since your table contains the relationship as well as the definition, you can replace @nodes with your table name...

Here's my shot:

Declare @tree table
(
	cNodeId int, 
	pNodeId int, 
	name varchar(50),
	processed bit default(0)
)
Insert Into @tree
Select 1, 0, 'England', 0 Union All
Select 2, 1, 'West Midlands', 0 Union All
Select 3, 2, 'Dudley', 0 Union All
Select 4, 3, 'Brierley Hill', 0 Union All
Select 5, 3, 'Merry Hill', 0


Declare @pad nvarchar(100),
@lastCnt int

Set @Pad = '0000'

Declare @paths table (path nvarchar(1000), pNodeId int, cNodeId int) 

Insert Into @paths
Select
     path=right(@pad + convert(nvarchar,pNodeId),len(@pad))+';' + right(@pad + convert(nvarchar,cNodeId),len(@pad))+';',
     pNodeId,
     cNodeId
From @Tree where pNodeId=0 

Update A
Set Processed = 1
From @Tree as A
Inner Join @paths as B
On A.pNodeId = B.pNodeId
and A.cNodeId = B.cNodeId

While exists(Select * From @tree Where Processed = 0) Begin      Insert Into @paths
     Select
          path=path + case when B.cNodeId is not null then right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';' else '' end,
          B.pNodeId,
          B.cNodeId
     From @Paths as A
     Left Join @Tree as B
     On A.cNodeId = B.pNodeId
     Where B.Processed = 0

     Update A
     Set Processed = 1
     From @Tree as A
     Inner Join @paths as B
     On A.pNodeId = B.pNodeId
     and A.cNodeId = B.cNodeId
     Where A.Processed = 0
End 

Select * From @paths

Declare @nodeId int
Set @nodeId = 3

Select distinct pNodeId = B.cNodeId, B.name, level = (charindex(right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';',A.Path)-1)/(len(@pad)+1)
From @paths as A, @Tree as B
Where A.path like
     '%'+right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';%'+right(@pad+convert(nvarchar,@NodeId),len(@pad))+';%'


Select distinct A.cNodeId, B.Name, level = (charindex(right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';',A.Path)-1)/(len(@pad)+1)
From @paths as A
Inner Join @tree as B
On A.cNodeId = B.cNodeId
Where A.path like '%' + right(@pad + convert(nvarchar,@NodeId),len(@pad))+';%'
and A.cNodeId <> @NodeId

I'm not sure exactly what you were going for with the children section... I would really just suggest the path table minus the current path... but let me know where you're at!?

---

replace:
Select distinct pNodeId = B.cNodeId, B.name, level = (charindex(right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';',A.Path)-1)/(len(@pad)+1)
From @paths as A, @Tree as B
Where A.path like
     '%'+right(@pad+convert(nvarchar,B.cNodeId),len(@pad))+';%'+right(@pad+convert(nvarchar,@NodeId),len(@pad))+';%'

with:

Select distinct pNodeId = C.cNodeId, C.name, Level = (len(A.Path)/(len(@pad)+1))-1
From @paths as A
Inner Join (Select path from @paths Where cNodeId=@NodeId) B
On B.path like A.path+'%'
Inner Join @Tree as C
On A.cNodeId = C.cNodeId
Where C.cNodeId<>@NodeId