How to split single cell into multiple columns in

I want to split each name for individual columns

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

Expected output

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

回答1:

using this example you can get an idea.

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

回答2:

Here is an sql statement using recursive CTE to split names into rows, then pivot rows into columns. SqlFiddle

with names as
(select 
  value,
  1 as name_id,
    substring(Allnames,1,charindex(',',Allnames+',', 0)-1)  as name,
    substring(Allnames,charindex(',',Allnames, 0)+1, 40) as left_names
from split_test
union all
select 
  value,
  name_id +1,
  case when charindex(',',left_names, 0)> 0 then
    substring(left_names,1,charindex(',',left_names, 0)-1) 
  else left_names end as name,
  case when charindex(',',left_names, 0)> 0 then
    substring(left_names,charindex(',',left_names, 0)+1, 40) 
  else '' end as left_names
from names
where ltrim(left_names)<>'')
select value,
  [1],[2],[3],[4],[5],[6],[7],[8],[9]
from (select value,name_id,name from names) as t1
PIVOT (MAX(name) FOR name_id IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9] ) ) AS t2

UPDATE

@KM.'s answer might be a better way to split data into rows without recursive CTE table. It should be more efficient than this one. So I follow that example and simplified the part of null value process logic. Here is the result:

Step 1: Create a table includes all numbers from 1 to a number grater than max length of Allnames column.

CREATE TABLE Numbers( Number  int   not null primary key);
with n as
(select 1 as num
union all
select num +1
from n
where num<100)
insert into numbers
select num from n;

Step 2: Join data of split_test table with numbers table, we can get all the parts start from ,. Then take the first part between 2 , form every row. If there are null values exists, add them with union.

select value , 
 ltrim(rtrim(substring(allnames,number+1,charindex(',',substring(allnames,number,40),2)-2))) as name
from
 (select value, ','+allnames+',' as allnames
  from split_test) as t1
left join numbers
on number<= len(allnames)
where substring(allnames,number,1)=',' 
 and substring(allnames,number,40)<>','
union
select value, Allnames
from split_test
where Allnames is null

Step 3: Pivot names from rows to columns like my first attempt above, omitted here. SQLFiddle