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
方法：（有三種）

  --牛逼的方法（悟其精髓確有難度）
  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;