sql - How to create loop based on value of row? -

i have problem when use query bellow have looping inside cursor.

data in table1 this:

id  |  data ----|---------   |  4 b   |  2 c   |  5 

the result in table2 should this:

id  |  data ----|---------   |  1   |  1   |  1   |  1 b   |  1 b   |  1 c   |  1 c   |  1 c   |  1 c   |  1 c   |  1 

i have sql query cursor this:

declare @table2 table ( id varchar(500), data integer)  declare cur cursor select id, data table1  open cur   while ( @@fetch_status = 0 )     begin         declare @loopnum integer         declare @tempid varchar(255)         declare @tempdata integer          fetch next cur @tempid, @tempdata          set @loopnum = 0          while @loopnum < @tempdata             begin             insert table2 (id, data)             values( @tempid, 1)             set @loopnum = @loopnum + 1         end     end  close cur  deallocate cur   select * table2 

but query didn't work. there wrong query? thank you.

use query expected result.

create table #test   (id   char(1),data int)  insert #test values ('a',4) insert #test values('b',2) insert #test values('c',5);   select s.id, 1 data #test s inner join  master.dbo.spt_values t on t.type='p'    , t.number between 1 , s.data 

note: refer why (and how) split column using master..spt_values?