一行变多行的有关问题
一行变多行的问题
现在有表如下
Table t1
f1 f2
A 4
B 8
我想得到如下的形式
A 1
A 1
A 1
A 1
B 1
B 1
B 1
B 1
B 1
B 1
B 1
B 1
------解决方案--------------------
with t1 as
(
select 'A' f1, 4 f2 from dual
union all
select 'B' f1, 8 f2 from dual
)
select t1.f1,1 f2
from t1,
(select rownum r1 from dual
connect by rownum < 100) t2
where t1.f2 >= t2.r1
order by t1.f1
------解决方案--------------------
使用游标来实现,没有测试过。
现在有表如下
Table t1
f1 f2
A 4
B 8
我想得到如下的形式
A 1
A 1
A 1
A 1
B 1
B 1
B 1
B 1
B 1
B 1
B 1
B 1
------解决方案--------------------
with t1 as
(
select 'A' f1, 4 f2 from dual
union all
select 'B' f1, 8 f2 from dual
)
select t1.f1,1 f2
from t1,
(select rownum r1 from dual
connect by rownum < 100) t2
where t1.f2 >= t2.r1
order by t1.f1
------解决方案--------------------
使用游标来实现,没有测试过。
- SQL code
declare @tb table(f1 varchar(10),f2 int) declare @f1 varchar(10),@f2 int declare cur_tmp cursor fast_forward for select f1,f2 from t1 open cur_tmp fetch next from cur_tmp into @f1,@f2 while @@fetch_status = 0 begin while @f2 > 0 begin insert into @tb select @f1,1 set @f2 = @f2 - 1 end fetch next from cur_tmp into @f1,@f2 end select * from @tb;
------解决方案--------------------
------解决方案--------------------