知识备忘—ORACLE纯粹行列转换

ORACLE 纯粹行列转换！！

转换前：
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
方法：（有三种）
  --1:牛X的方法（悟其精髓确有难度）
  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);
  --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;
  --3:方法太多了
  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;

（注明）以上为网络搜集资料
--4:我的方法：
select sum(decode(rn, 1, b, null))/6 id1,
       sum(decode(rn, 2, b, null))/6 id2,
       sum(decode(rn, 3, b, null))/6 id3,
       sum(decode(rn, 4, b, null))/6 id4,
       sum(decode(rn, 5, b, null))/6 id5,
       sum(decode(rn, 6, b, null))/6 id6
from test