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

try this: [split comma separated values into distinct rows](http://stackoverflow.com/questions/7882623/split-comma-separated-values-into-distinct-rows) — SajjadHashmi, Dec 11 '12 at 10:01
duplicate of http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Gangnus, Dec 11 '12 at 10:03

Sagar Modi · Answer 1 · 2012-12-11T10:22:37.800

2

    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);

edited Dec 11 '12 at 10:22

answered Dec 11 '12 at 10:13

Sagar Modi

770
2
7
29

score 0 · Accepted Answer · edited May 23 '17 at 10:34

0

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

edited May 23 '17 at 10:34

Community

1
1

answered Dec 11 '12 at 09:58

JIbber4568

839
4
16
33

score 0 · Answer 3 · answered Dec 11 '12 at 10:18

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

sql multiple row from column value

3 Answers3