sql一条数据拆分为多条数据

[u011092509]

比如：数据表结构为:
id username userCount
1 aa 2
2 a 1
3 aa 3

如何sql语句查询得到数据为:
id username userCount
1 aa 1
2 aa 1
3 a 1
4 aa 1
5 aa 1
6 aa 1

[u011092509]

求解？大家回复？

[唐诗三百首]

create table u01
(id int, username varchar(5), userCount int)

insert into u01
 select 1, 'aa', 2 union all
 select 2, 'a', 1 union all
 select 3, 'aa', 3
 

create table #t
(id int not null identity(1,1),
 username varchar(5),
 userCount int
)

declare @id int,@username varchar(5),@userCount int,@i int

declare ap scroll cursor for
 select id,username,userCount from dbo.u01

open ap

fetch first from ap into @id,@username,@userCount
while(@@fetch_status<>-1)
begin
 select @i=1
 while(@i<=@userCount)
 begin
   insert into #t(username,userCount) values(@username,1)
   select @i=@i+1
 end
 fetch next from ap into @id,@username,@userCount
end

close ap
deallocate ap


-- 结果
select id,username,userCount from #t

/*
id          username userCount
----------- -------- -----------
1           aa       1
2           aa       1
3           a        1
4           aa       1
5           aa       1
6           aa       1

(6 row(s) affected)
*/