oracle 根据行列号取值

//数据 A B C -- -- -- a1 b1 c1 a2 b2 c2 a3 b3 c3 //结果: row col value -- --- ----- 1 1 a1 1 2 b1 1 3 c1 2 1 a2 2 2 b2 2 3 c2 3 1 a3 3 2 b3 3 3 c3 -- with tb as( select 'a1' a,'b1' b,'c1' c from dual union all select 'a2','b2','c2' from dual union all select 'a3','b3','c3' from dual) select rn,1,a from (select a,b,c,rownum as rn from tb ) union select rn,2,b from (select a,b,c,rownum as rn from tb ) union select rn,3,c from (select a,b,c,rownum as rn from tb ) -- //解析： //通过执行子查询，从结果中我们看到了，对于每一行都返回了一个rn(每行行号)； //对于每一个行号，执行上面的select union查询，每一次union，都返回一个结果集； //连接指定的1,2,3,和每一个rn对应的a，b，c值，就得到了我们想要的结果。 //例如rn=1时： 1 1 a1 1 2 b1 1 3 c1 -- SQL> with tb as( 2 select 'a1' a,'b1' b,'c1' c from dual union all 3 select 'a2','b2','c2' from dual union all 4 select 'a3','b3','c3' from dual) 5 select a,b,c,rownum as rn from tb 6 / A B C RN -- -- -- ---------- a1 b1 c1 1 a2 b2 c2 2 a3 b3 c3 3

原帖：http://topic.csdn.net/u/20110424/14/f9ffdc77-2372-4d41-b7bf-96f64fa0cfb6.html?seed=747954903&r=72952641#r_72952641