只能5列,实际是需要固定列 select 'nn',max(decode(rn,1,no1,null)) as col1, max(decode(rn,2,no1,null)) as col2, max(decode(rn,3,no1,null)) as col3, max(decode(rn,4,no1,null)) as col4, max(decode(rn,5,no1,null)) as col5 From (select no1,row_number() over(Order By no1) As rn from TEST ) group by 'nn'
仅供参考,希望对你有点帮助。 insert into table4 select a, b, c, d, e from (select t1.no1 e, t2.no1 d, t3.no1 c, t4.no1 b, t5.no1 a from table3 t1, table3 t2, table3 t3, table3 t4, table3 t5 where ((t5.no1 != t4.no1 and t5.no1 != t3.no1 and t5.no1 != t2.no1 and t5.no1 != t1.no1) and (t4.no1 != t3.no1 and t4.no1 != t2.no1 and t4.no1 != t1.no1) and (t3.no1 != t2.no1 and t3.no1 != t1.no1) and t2.no1 != t1.no1) and rownum = 1); commit work;
写个思路 创建一个存储过程 1、新建游标 2、根据游标记录的行数生成:insert into 表(a1,a2.....)段语句 3、根据游标记录生成values(value1,value2,.....) 4、利用动态SQL执行这个语句变量
行列转置,可以看看sql server里的贴
select 'nn',max(decode(rn,1,no1,null)) as col1, max(decode(rn,2,no1,null)) as col2, max(decode(rn,3,no1,null)) as col3, max(decode(rn,4,no1,null)) as col4, max(decode(rn,5,no1,null)) as col5 From ( select no1,row_number() over(Order By no1) As rn from TEST ) group by 'nn' 用到了分析函数 是最好的解决办法 经典
select 'nn',max(decode(rn,1,no1,null)) as col1,
max(decode(rn,2,no1,null)) as col2,
max(decode(rn,3,no1,null)) as col3,
max(decode(rn,4,no1,null)) as col4,
max(decode(rn,5,no1,null)) as col5 From (select no1,row_number() over(Order By no1) As rn from TEST )
group by 'nn'
no1
1c
2c
3c
4c
5c
怎么这列数据更新到数据库
例如 table a 结果是这样
1c 2c 3c 4c 5c
有啥好办法?
insert into table4
select a, b, c, d, e
from (select t1.no1 e, t2.no1 d, t3.no1 c, t4.no1 b, t5.no1 a
from table3 t1, table3 t2, table3 t3, table3 t4, table3 t5
where ((t5.no1 != t4.no1 and t5.no1 != t3.no1 and t5.no1 != t2.no1 and t5.no1 != t1.no1) and
(t4.no1 != t3.no1 and t4.no1 != t2.no1 and t4.no1 != t1.no1) and
(t3.no1 != t2.no1 and t3.no1 != t1.no1) and
t2.no1 != t1.no1) and
rownum = 1);
commit work;
创建一个存储过程
1、新建游标
2、根据游标记录的行数生成:insert into 表(a1,a2.....)段语句
3、根据游标记录生成values(value1,value2,.....)
4、利用动态SQL执行这个语句变量
max(decode(rn,2,no1,null)) as col2,
max(decode(rn,3,no1,null)) as col3,
max(decode(rn,4,no1,null)) as col4,
max(decode(rn,5,no1,null)) as col5 From ( select no1,row_number() over(Order By no1) As rn from TEST )
group by 'nn'
用到了分析函数 是最好的解决办法 经典