Run this Functions:
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
and
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
)
You expected Result Here :
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
Store the Results in any Temp Table and Select All
better you make this as Stored Procedure :)