如何在sql server 2008R2中将单个单元格拆分为多个列？

我想将每个名称拆分为各个列

create table split_test(value integer,Allnames varchar(40))
insert into split_test values(1,'Vinoth,Kumar,Raja,Manoj,Jamal,Bala');
select * from split_test;

Value   Allnames
-------------------
1       Vinoth,Kumar,Raja,Manoj,Jamal,Bala

预期产出

values  N1     N2     N3      N4    N5      N6    N7.......N20
1      Vinoth  Kumar  Raja   Manoj  Jamal   Bala

使用这个例子你可以得到一个想法。

declare @str varchar(max)
set @str = 'Hello world'

declare @separator varchar(max)
set @separator = ' '

declare @Splited table(id int identity(1,1), item varchar(max))

set @str = REPLACE(@str,@separator,'''),(''')

set @str = 'select * from (values('''+@str+''')) as V(A)' 

insert into @Splited
exec(@str)
select * from @Splited