--try:select pj,deocde(no,1,'A',2,'B',3,'C') fieldname,
max(decode(no,1,a,null)) col1,
max(decode(no,2,a,null)) col2,
max(decode(no,3,a,null)) col3
from
(
select rownum no,pj,a from tb union all
select rownum,pj,b from tb union all
select rownum,pj,c from tb
)
group by pj,deocde(no,1,'A',2,'B',3,'C');
max(decode(no,1,a,null)) col1,
max(decode(no,2,a,null)) col2,
max(decode(no,3,a,null)) col3
from
(
select rownum no,pj,a from tb union all
select rownum,pj,b from tb union all
select rownum,pj,c from tb
)
group by pj,deocde(no,1,'A',2,'B',3,'C');
只是选出了所有数据中最大的值
max(decode(no,1,a,null)) col1,
max(decode(no,2,a,null)) col2,
max(decode(no,3,a,null)) col3
from
(
select 1 rn,rownum no,pj,a from tb union all
select 2,rownum,pj,b from tb union all
select 3,rownum,pj,c from tb
)
group by pj,decode(rn,1,'A',2,'B',3,'C')
/
一定要用马?
谢谢 ORARichard(没钱的日子......)
是什么样的问题?这种写法只能适应你举的例子,不具有扩展性。就你的例子我测试是没问题的。SQL817> select * from tb;PJ A B C
---- ---- ---- ----
PJ1 a1 b1 c1
PJ1 a2 b2 c2
PJ1 a3 b3 c3已用时间: 00: 00: 00.30
SQL817> select pj,decode(rn,1,'A',2,'B',3,'C') fieldname,
2 max(decode(no,1,a,null)) col1,
3 max(decode(no,2,a,null)) col2,
4 max(decode(no,3,a,null)) col3
5 from
6 (
7 select 1 rn,rownum no,pj,a from tb union all
8 select 2,rownum,pj,b from tb union all
9 select 3,rownum,pj,c from tb
10 )
11 group by pj,decode(rn,1,'A',2,'B',3,'C')
12 /PJ F COL1 COL2 COL3
---- - ---- ---- ----
PJ1 A a1 a2 a3
PJ1 B b1 b2 b3
PJ1 C c1 c2 c3已用时间: 00: 00: 00.90
改成(group by pj,rn)就不出错了,
这样改,逻辑应该没有什么问题吧?