Пекурсия. Обойти все узлы

С использованием рекурсивной функции без труда сделал запрос:

if not object_id('VEHICLE_TREE_TEST') is null
	drop table VEHICLE_TREE_TEST

go

create table VEHICLE_TREE_TEST (
	VTR_NODE_ID int NOT NULL PRIMARY KEY,
	VTR_PARENT_NODE_ID int NULL DEFAULT (null)
)

insert into VEHICLE_TREE_TEST (VTR_NODE_ID, VTR_PARENT_NODE_ID)
select 100, null union 
select 101, 100 union
select 103, 101 union
select 104, 101 union
select 105, 104 union
select 102, 100 union
select 106, 102 union
select 108, 106 union
select 107, 102 
go


if not object_id('GetChildsTemp') is null
	drop function GetChildsTemp
go

create function GetChildsTemp(@prnt as int)
returns varchar(4000)
as
begin
	declare @PKCols varchar(4000), @chld int

	set @PKCols = ''

	select	@PKCols = coalesce(@PKCols, N'') + N',' + cast(VTR_NODE_ID as varchar)
	from VEHICLE_TREE_TEST
	where VTR_PARENT_NODE_ID = @prnt

	declare cur cursor
	for
	select  VTR_NODE_ID
	from VEHICLE_TREE_TEST (nolock)
	where VTR_PARENT_NODE_ID = @prnt

	open cur

	FETCH NEXT FROM cur into @chld

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		set @PKCols = @PKCols + dbo.GetChildsTemp(@chld)

		FETCH NEXT FROM cur into @chld

	END

	CLOSE cur
	DEALLOCATE cur

	return @PKCols

end
go


select VTR_NODE_ID as [Root], case when len(List) = 0 then List else right(List, len(List) - 1) end as List
from (
	select VTR_NODE_ID, dbo.GetChildsTemp(VTR_NODE_ID) as List
	from VEHICLE_TREE_TEST ) as T

И получил, то, что и требовалось по ТЗ:

Root List
100 101,102,103,104,105,000,000,000
101 103,104,105
102 106,107,108
103
104 105
105
106 108
107
108

Пытался сделать запрос в терминах SQL Server 2005 с использованием cte, но, к сожалению, ничего не вышло.
Покажите, как это можно сделать
Спасибо!

declare @VEHICLE_TREE_TEST table (
	VTR_NODE_ID int NOT NULL PRIMARY KEY,
	VTR_PARENT_NODE_ID int 
)

insert into @VEHICLE_TREE_TEST (VTR_NODE_ID, VTR_PARENT_NODE_ID)
select 100, null union 
select 101, 100 union
select 103, 101 union
select 104, 101 union
select 105, 104 union
select 102, 100 union
select 106, 102 union
select 108, 106 union
select 107, 102

;WITH CTE (VTR_NODE_ID, level) AS 
(
SELECT VTR_NODE_ID, 1 AS LEVEL
FROM @VEHICLE_TREE_TEST
WHERE VTR_PARENT_NODE_ID IS NULL
UNION ALL
SELECT T.VTR_NODE_ID, C.LEVEL + 1
FROM @VEHICLE_TREE_TEST T
INNER JOIN CTE C ON T.VTR_PARENT_NODE_ID = C.VTR_NODE_ID  
)
SELECT * 
FROM CTE 
ORDER BY LEVEL 

msLex

не дочитал че надо, это просто обход дерева.

msLex,
Такое я получаю, научился уже

with Q (a, b, c) as
(
  select VTR_NODE_ID, VTR_NODE_ID, cast('' as varchar(max))
  from VEHICLE_TREE_TEST

  union all
  select Q.a, A.VTR_NODE_ID, Q.c + case Q.c when '' then '' else ',' end +cast(A.VTR_NODE_ID as varchar)
  from VEHICLE_TREE_TEST A
    join Q on A.VTR_PARENT_NODE_ID = Q.b
), X(a, b, c) as
(
  select a, c, row_number() over(partition by a order by len(c) desc)
  from Q
)
select a, b from X
where c = 1
order by 1

Зайцев Фёдор,
Ближе, чем у меня получалось, но, к сожалению, не верно.
Для рута надо обойти абсолютно все узлы, для каждого из детей - обойти всех его наследников до последнего колена

Rivkin Dmitry

Зайцев Фёдор, Ближе, чем у меня получалось, но, к сожалению, не верно. Для рута надо обойти абсолютно все узлы, для каждого из детей - обойти всех его наследников до последнего колена

это у вас не верно.
000,000,000 - кто это такие вообще?

Зайцев Фёдор

Rivkin Dmitry Зайцев Фёдор, Ближе, чем у меня получалось, но, к сожалению, не верно. Для рута надо обойти абсолютно все узлы, для каждого из детей - обойти всех его наследников до последнего колена это у вас не верно. 000,000,000 - кто это такие вообще?

Верно, не заметил, что вкралась ошибка при коприровании в форум. Читать надо так:

Root List
100 101,102,103,104,105,106,107,108
101 103,104,105
102 106,107,108
103
104 105
105
106 108
107
108

with Q (a, b, c) as
(
  select VTR_NODE_ID, VTR_NODE_ID, cast(null as varchar(max))
  from VEHICLE_TREE_TEST
  
  union all
  select Q.a, A.VTR_NODE_ID, cast(A.VTR_NODE_ID as varchar(max))
  from VEHICLE_TREE_TEST A
    join Q on A.VTR_PARENT_NODE_ID = Q.b
)
select a, dbo.clr_AggrConcat(distinct c)
from Q
group by a
order by 1

где clr_AggrConcat - некая ваша агрегатная функция

Зайцев Фёдор,
Я еще с такими функциями не знакам. Можно ткнуть меня носом куда смотреть? Или можно привести пример?