How to split single cell into multiple columns in sql server 2008R2?

Question

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

This is a very, very bad design. Please normalize your table. — Erran Morad, Sep 02 '14 at 04:49
Search google for : sql server convert csv to rows. http://www.codeproject.com/Tips/732596/Converting-comma-separated-data-in-a-column-to-row — Erran Morad, Sep 02 '14 at 04:56
possible duplicate of [How do I expand comma separated values into separate rows using SQL Server 2005?](http://stackoverflow.com/questions/702968/how-do-i-expand-comma-separated-values-into-separate-rows-using-sql-server-2005) — Erran Morad, Sep 02 '14 at 04:56
Unless you have an upper bound of max splits, you need dynamic SQL. And this is a _terrible_ design (the fact that you're trying to split it into actual columns should have been your first clue). — Clockwork-Muse, Sep 02 '14 at 11:18

score 0 · Answer 1 · answered Sep 02 '14 at 12:23

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

score 0 · Answer 2 · edited May 23 '17 at 12:12

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

If you look around the question, you can find the number of values are not fixed (N1, N2...), so you can't decide it up to 6 levels. It may be more or less... — ram_sql, Sep 02 '14 at 14:21

How to split single cell into multiple columns in sql server 2008R2?

2 Answers2

Linked