sql - Split string and number columns -

let's have table such as

itemid        classid ------------------------ 1             10, 13, 12 2             5, 7 

and copy data table so

itemid     numbering       classid ---------------------------------- 1          1               10 1          2               13 1          3               12 2          1               5 2          2               7 

1. separating comma-delimited classid field individual rows, retaining order had in first table
2. populating numbering row on insert. numbering column has sequential integers each batch of classid , why classid needs kept in order.

i have attempted following function:

  create function dbo.split    (       @string nvarchar(max)    )   returns @splittedvalues table(    value int   )   begin   declare @splitlength int   declare @delimiter varchar(10)    set @delimiter = ','    while len(@string) > 0    begin     select @splitlength = (case charindex(@delimiter, @string)          when 0            datalength(@string) / 2          else            charindex(@delimiter, @string) - 1        end)     insert @splittedvalues    select cast(substring(@string, 1, @splitlength) integer)       ltrim(rtrim(isnull(substring(@string, 1, @splitlength), ''))) <> '';     select @string = (case ((datalength(@string) / 2) - @splitlength)          when 0            ''          else            right(@string, (datalength(@string) / 2) - @splitlength - 1)        end)    end   return  end 

but partly works. copies rows correct amount of times (i.e. 3 times itemid=1, , twice itemid=2 in above example), exact copies of row (all saying '10, 13, 12') , comma-delimited parts not split up. there nothing in function add numbering column.

so, have 2 questions: how modify above function split classid string, , add correctly increment numbering column?

thanks!

i'd use recursive cte it.

with splitcte (     select         itemid,         left(classid,charindex(',',classid)-1) classid         ,right(classid,len(classid)-charindex(',',classid)) remaining         table1         classid not null , charindex(',',classid)>0     union     select         itemid,         left(remaining,charindex(',',remaining)-1)         ,right(remaining,len(remaining)-charindex(',',remaining))         splitcte         remaining not null , charindex(',',remaining)>0     union     select         itemid,remaining,null         splitcte         remaining not null , charindex(',',remaining)=0  ) select   itemid,  row_number() on (partition itemid order cast(classid int) asc) numbering,  cast (classid  int) classid   splitcte    union  select   itemid,  1,  cast(classid int)  table1  classid not null , charindex(',',classid) = 0 

sql fiddle