To get the Parent id list of an advisor

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 pid from a
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.

OK, I've read through all the replies. Your question is HORRIBLE, it's not remotely clear. According to the comments, you want to write a recursive query. You can do that using CTEs, here[^] is my article on how they work. If you then want to get that list in to one cell, you will need to use the XML approach Griff showed you, once you've got the table of data to work with.

I strongly suspect you're not going to read any articles and just want us to do all the work for you. That is impossible. We can't really do more without the schema of your table, example data, and examples of the results you want to get out, so we can see what exactly you want, and where you want it from.

While loop not required
check this example

WITH tbl_advisor_registration as
(
select 1 create_by_user_id, 1 as advisor_id UNION ALL
select 2 create_by_user_id, 1 as advisor_id UNION ALL
select 3 create_by_user_id, 1 as advisor_id UNION ALL
select 4 create_by_user_id, 1 as advisor_id UNION ALL
select 5 create_by_user_id, 1 as advisor_id UNION ALL
select 6 create_by_user_id, 2 as advisor_id UNION ALL
select 7 create_by_user_id, 2 as advisor_id
)
Select Stuff(
                  (
                    Select ', ' + Convert(varchar(5),create_by_user_id)
                        from tbl_advisor_registration
                        where advisor_id = 1 --@intro_id
                        For Xml Path('')
                  ), 1, 2, ''
            )

Happy coding!
:)