请教各位高手一个问题
如何才能实现以下的效果,不论用什么方法都行,希望各位高手相告,不胜感激
在一个表中,如下
A列 B列 C列
1 2 2
a b c
x y z
4 5 6
7 8 9
转换成:
A列 B列 C列 A1列 B1列 C1列
1 2 2 a b c
x y z 4 5 6
7 8 9
如何才能实现以下的效果,不论用什么方法都行,希望各位高手相告,不胜感激
在一个表中,如下
A列 B列 C列
1 2 2
a b c
x y z
4 5 6
7 8 9
转换成:
A列 B列 C列 A1列 B1列 C1列
1 2 2 a b c
x y z 4 5 6
7 8 9
(
select * from (
select t.*,rownum rn from test t order by colb )
where mod(rn,2)=0
) a
,
(
select * from (
select t.*,rownum rn from test t order by colb )
where mod(rn,2)=1
) b
where a.rn=b.rn+1方法不好,暂时没想出来好的
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
SQL>select sum(decode(rnum,1,a,null))col1,sum(decode(rnum,1,b,null))col2,sum(decode(rnum,1,c,null))col3,
sum(decode(rnum,2,a,null))col4,sum(decode(rnum,2,b,null))col5,sum(decode(rnum,2,c,null))col6
from (select rownum rnum,a,b,c from test)
union
select sum(decode(rnum,3,a,null)),sum(decode(rnum,3,b,null)),sum(decode(rnum,3,c,null)),
sum(decode(rnum,4,a,null)),sum(decode(rnum,4,b,null)),sum(decode(rnum,4,c,null))
from (select rownum rnum,a,b,c from test)
union
select sum(decode(rnum,5,a,null)),sum(decode(rnum,5,b,null)),sum(decode(rnum,5,c,null)),null,null,null
from (select rownum rnum,a,b,c from test); COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15
select
decode(mod(rnum,2),1,a1,null) col1,
decode(mod(rnum,2),1,a2,null) col2,
decode(mod(rnum,2),1,a3,null) col3,
decode(mod(rnum,2),0,a1,null) col4,
decode(mod(rnum,2),0,a2,null) col5,
decode(mod(rnum,2),0,a3,null) col6
from (select rownum rnum,a1,a2,a3 from test1 order by to_number(a1)) COL1 COL2 COL3 COL4 COL5 COL6
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
我想得出的数据是
COL1 COL2 COL3 COL4 COL5 COL6
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15请两位再指点指点,明天我再加100
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15 上面的格式没弄好
from
(select a.a1,a.a2,a.a3,a.rn+1 as rn from(select t.a1,t.a2,t.a3,rownum rn from(select * from test1 t order by to_number(t.a1)) t) a where mod(rn,2) <> 0) x1,
(select b.a1,b.a2,b.a3,b.rn from(select t.a1,t.a2,t.a3,rownum rn from(select * from test1 t order by to_number(t.a1)) t) b where mod(rn,2) = 0) x2
where x1.rn = x2.rn(+) 可以了,多谢二位的指点啦