Recursive SQL

[Guest_imported]

The name of the Tabe is Tab_Sponsor with columns
Sponsor Numeric(6) NOT NULL
Name Varchar(30) NOT NULL
Reporting_To Numeric(6) NULL

The records for the above table are as below

Sponsor Name Reporting_To
1 Tony -
2 Ricard 1
3 Bob 2
4 David 2
5 Harry 2
6 Raymond 4
7 Rex 6
8 Viv 6

If I want to all the Persons Reporting to 2 then it should return me 3,4,5,6(6->4->2),7,8

Can you please help me in formulating the query please

 

[1209]

Can try with this

Declare @str varchar(1000), @str1 varchar(1000)

set @str=''

Select @str=@str + Name + ',' from Tab_Sponsor where Reporting_To = 2

Select @str=substring(@str,1,datalength(@str)-1)

set @str1=@str + ','

While Datalength(@str1) > Datalength(@str)
begin
/*it will be a dynamic query*/
select @str1=@str1 + Name + ',' from Tab_Sponsor where Reporting_To in (@str)

Select @str=substring(@str1,1,datalength(@str1)-1)
end

Dont know if it will work but can try