WITH cte AS ( SELECT child FROM table WHERE parent = @starting_node
              UNION ALL
              SELECT table.child FROM table, cte WHERE table.parent = cte.child )
SELECT child FROM cte

declare @tree table (parent varchar(5), child varchar(5))
insert into @tree values('a1','a2')
insert into @tree values('a1','a3')
insert into @tree values('a1','a4')
insert into @tree values('a2','a5')
insert into @tree values('a2','a6')
insert into @tree values('a3','a7')
insert into @tree values('a5','a10')
insert into @tree values('a6','a11')
insert into @tree values('a4','a8')
insert into @tree values('a4','a9')
insert into @tree values('a8','a12')
insert into @tree values('a9','a13')
insert into @tree values('a9','a12')
insert into @tree values('a10','a14')
insert into @tree values('a10','a15')
insert into @tree values('a11','a16')
insert into @tree values('a11','a17')
insert into @tree values('a11','a18')
insert into @tree values('a12','a3')
insert into @tree values('a17','a15')
insert into @tree values('a18','a7')
insert into @tree values('a13','a19')
insert into @tree values('a14','a4')
insert into @tree values('a13','a4')

declare @a varchar(5)='a1'

;with cte as (
	select 
		node	=child
		,[path]	=cast('/'+child+'/' as varchar(max))
	from @tree 
	where parent=@a

	union all

	select 
		node	=t.child
		,[path]	=cast(cte.[path]+t.child+'/' as varchar(max))
	from @tree t inner join cte on t.parent=cte.node
	where cte.[path] not like '%/'+t.child+'/%'
)

select 
	* 
	/* distinct node */
from cte  
order by 1