How Can I Get Record By Spliting

create FUNCTION [dbo].[Split]
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	Insert Into @RtnValue (Value)
	Select Value = ltrim(rtrim(@List))
	Return
END

CREATE FUNCTION [dbo].[Split1]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

drop table #table
 create  table #table (id int,data nvarchar(100));

 declare @start int =1,@end int=10,@str1 nvarchar(max),@reset int=1,@intial int=1;
 declare @Str varchar(8000) = 'Paul Cameron Adi Hardy';
 insert into #table(id,data ) select * from dbo.Split1(@str,' ')
 while(@start<=@end)
 begin

 SELECT @str1= STUFF((SELECT ',' + cast(id as nvarchar(max)) AS [text()] FROM #table where id >=@intial and  id<=@reset FOR XML PATH('') ), 1, 1, '' ) ;

 SELECT STUFF((SELECT ' ' + cast(data as nvarchar(max)) AS [text()] FROM #table where id in(select value from dbo.Split(@str1,',')) FOR XML PATH('') ), 1, 1, '' )  as advisor_id

 set @start=@start+1;
 if(@reset=4)
 begin
 set @reset=0;
 set @intial=@intial+1;
 if(@intial>@reset)
 begin
 set @reset=@intial;
end
 end
 else
 begin
 set @reset=@reset+1;
 end
 end

declare @s varchar(500) = 'Paul Cameron Adi Hardy'
set @s=@s+' '

declare @Table table (name varchar(100),leveling int,SNo int identity(1,1) PRIMARY KEY)

declare @ni int
set @ni = charindex(' ',@s,0)

;with cte (name,spaceindex,namenew,leveling)
as
(
	select substring(@s,0,@ni),@ni,substring(@s,@ni+1,LEN(@s)-1),1
	union all
	select substring(namenew,0,ni.ni),ni.ni,substring(namenew,ni.ni+1,LEN(namenew)-1),leveling+1
	from cte
		cross apply (select charindex(' ',namenew,0) ni)ni
	where charindex(' ',namenew,0)>0
)
insert into @Table
select name,leveling from cte

declare @MaxCount int,@MinCount int
select @MaxCount = max(leveling),@MinCount=min(leveling) from @Table

declare @Count int
set @Count=1

declare @tempName varchar(200)=null

while @MaxCount>0
begin
	if (select max(leveling) from @Table)=@Count-1
	begin
		set @tempName=null
		set @Count=@MinCount+1
		set @MinCount=@MinCount+1
		set @MaxCount=@MaxCount-1
	end
	
	select @tempName=isnull(@tempName+' ','')+name from @Table where SNo=@Count
	print @tempName
	
	select @Count=@Count+1
end