with sview as (select * from (select 'NO1' A, '2' B, '3' C, '4' D from dual union all select 'NO2' A, '6' B, '7' C, '8' D from dual union all select 'NO3' A, '10' B, '11' C, '12' D from dual ) ) select * from (select * from sview unpivot(c for 类型 in ("B", "C", "D")) )pivot(max(c) for A in ('NO1', 'NO2', 'NO3')) order by 类型;参考http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/11g-pivot.html
用法搜索下就会用了
select 'B' 类型,decode(a,'NO1',b,0) NO1,
decode(a,'NO2',b,0) NO2,
decode(a,'NO3',b,0) NO3
from test_05
/就是太麻烦了
http://topic.csdn.net/u/20080320/21/815EA948-CC17-42C2-8F94-F7946C77225D.htmlhttp://topic.csdn.net/t/20051216/10/4462788.htmlhttp://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html也可以自己搜一下例子 研究研究!
这样好像只能处理3列哟,如果不固定列好像就不行了,估计用procedure才行吧
with sview as
(select *
from (select 'NO1' A, '2' B, '3' C, '4' D
from dual
union all
select 'NO2' A, '6' B, '7' C, '8' D
from dual
union all
select 'NO3' A, '10' B, '11' C, '12' D
from dual
)
)
select *
from (select *
from sview unpivot(c for 类型 in ("B", "C", "D"))
)pivot(max(c) for A in ('NO1', 'NO2', 'NO3'))
order by 类型;参考http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/11g-pivot.html