Oracle 根據(jù)行列號(hào)取值

發(fā)布時(shí)間: 2012/9/11 17:18:52

//數(shù)據(jù)
A   B C
-- -- --
a1 b1 c1
a2 b2 c2
a3 b3 c3
//結(jié)果:   -

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 )
--
//解析：
//通過(guò)執(zhí)行子查詢，從結(jié)果中我們看到了，對(duì)于每一行都返回了一個(gè)rn(每行行號(hào))；
//對(duì)于每一個(gè)行號(hào)，執(zhí)行上面的select union查詢，每一次union，都返回一個(gè)結(jié)果集；
//連接指定的1,2,3,和每一個(gè)rn對(duì)應(yīng)的a，b，c值，就得到了我們想要的結(jié)果。
//例如rn=1時(shí)：
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