Here it is :
declare @id int=7;
declare @tmp varchar(max)='';
declare @t1 table(
advisor_id int,name varchar(50),parent_id int)
insert into @t1 values
(1,'Griff',0),(2,'christian',1),(3 ,'AArti',2),
(4,'king_fisher',3),(5,'jason',4),(6,'smith',5),(7, 'willis' ,6);
with a(id , pid)
as
(select advisor_id,parent_id from @t1 where advisor_id=@id
union all
select a.id, t.parent_id from a inner join @t1 t on a.pid = t.advisor_id
)
select @tmp = @tmp + cast(pid as nvarchar(max)) + ', ' from a
select SUBSTRING(@tmp, 0, LEN(@tmp))
Change @id value to see what happens.
Good Luck.