sql multiple row from column value

Question

i have table with one column having comma seperated values and I want in row..

like

col1
3,4,5
5,6,6

return result should be

col1
3
4
5
5
6
6

Answer 1

    declare @tbl table(data nvarchar(100))
    insert into @tbl values('1,2,3')
    insert into @tbl values('4,5,6')
    insert into @tbl values('7,8,9')

     SELECT   
     Split.a.value('.', 'VARCHAR(100)') AS String  
     FROM  (SELECT data,  
         CAST ('<M>' + REPLACE(data, ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @tbl) AS A CROSS APPLY String.nodes ('/M') AS Split(a);  

Answer 2

I believe the below explains how to loop through comma separated values. You could just insert them into another variable to get your required output.

Splitting of comma separated values

Answer 3

You can use a recursive query to perform this split:

;with cte (item, list) as
(
  select 
    cast(left(col1, charindex(',',col1+',')-1) as varchar(50)) item,
         stuff(col1, 1, charindex(',',col1+','), '') list
  from yourtable
  union all
  select 
    cast(left(list, charindex(',',list+',')-1) as varchar(50)) item,
    stuff(list, 1, charindex(',',list+','), '') list
  from cte
  where list > ''
) 
select item
from cte

See SQL Fiddle with Demo