oracle行列转置转换

纯粹是为自己以后使用方便；

本文系转载：http://www.cnblogs.com/fjfzhkb/archive/2007/11/30/978307.html

感谢网友提供！

 

 

转换前：
select * from test；
A  B  C
-- -- --
1  2  3
2  2  4
4  3  3
3  1  6
4  3  6
6  5  9
8  8  8
转换后：
A            B              C               D             E
------------ -------------- --------------- ------------- -------------
1            2              4               3             4
2            2              3               1             3
3            4              3               6             6
方法：（有三种）

  --牛逼的方法（悟其精髓确有难度）
  select regexp_substr(str, '[^,]+', 1, 1) a,

         regexp_substr(str, '[^,]+', 1, 2) b,
         regexp_substr(str, '[^,]+', 1, 3) c,
         regexp_substr(str, '[^,]+', 1, 4) d,
         regexp_substr(str, '[^,]+', 1, 5) e
    from (select regexp_substr(str, '[^(\.)]+', 1, rownum) str
            from (select max(a) || '.' || max(b) || '.' || max(c) str
                    from (select substr(sys_connect_by_path(a, ','), 2) a,
                                 substr(sys_connect_by_path(b, ','), 2) b,
                                 substr(sys_connect_by_path(c, ','), 2) c
                            from (select rownum child,
                                         a,
                                         b,
                                         c,
                                         lead(rownum, 1) over(order by rownum) parent
                                    from test) t
                           start with child = 1
                          connect by prior parent = child))
          connect by rownum < length(regexp_replace(str, '[^(\.)]', '')) + 2);
  --正常的方法（大家都想的到）         
  select *
    from (select a a1,
                 lead(a, 1) over(order by rownum) a2,
                 lead(a, 2) over(order by rownum) a3,
                 lead(a, 3) over(order by rownum) a4,
                 lead(a, 4) over(order by rownum) a5,
                 lead(a, 5) over(order by rownum) a6
            from test)
   where rownum = 1
  union
  select *
    from (select b a1,
                 lead(b, 1) over(order by rownum) a2,
                 lead(b, 2) over(order by rownum) a3,
                 lead(b, 3) over(order by rownum) a4,
                 lead(b, 4) over(order by rownum) a5,
                 lead(b, 5) over(order by rownum) a6
            from test)
   where rownum = 1
  union
  select *
    from (select c a1,
                 lead(c, 1) over(order by rownum) a2,
                 lead(c, 2) over(order by rownum) a3,
                 lead(c, 3) over(order by rownum) a4,
                 lead(c, 4) over(order by rownum) a5,
                 lead(c, 5) over(order by rownum) a6
            from test)
   where rownum = 1;
  --方法太多了（你对oracle熟悉么？）
  select max(decode(rn, 1, a, null)) id1,
         max(decode(rn, 2, a, null)) id2,
         max(decode(rn, 3, a, null)) id3,
         max(decode(rn, 4, a, null)) id4,
         max(decode(rn, 5, a, null)) id5,
         max(decode(rn, 6, a, null)) id6
    from (select a.*, rownum rn from test a)
  union
  select max(decode(rn, 1, b, null)) id1,
         max(decode(rn, 2, b, null)) id2,
         max(decode(rn, 3, b, null)) id3,
         max(decode(rn, 4, b, null)) id4,
         max(decode(rn, 5, b, null)) id5,
         max(decode(rn, 6, b, null)) id6
    from (select a.*, rownum rn from test a)
  union
  select max(decode(rn, 1, c, null)) id1,
         max(decode(rn, 2, c, null)) id2,
         max(decode(rn, 3, c, null)) id3,
         max(decode(rn, 4, c, null)) id4,
         max(decode(rn, 5, c, null)) id5,
         max(decode(rn, 6, c, null)) id6
    from (select a.*, rownum rn from test a);
end p_test_row;