Oracle行列转换问题

今天又有朋友讨论以下行列转换问题:
      编码  行  值
      AA  　1  345
      BB  　1  456
      CC  　1  657
      DD  　2  546
      EE  　2  434
      FF  　2  897
      GG  　3  566 要实现如下转换
        1      2       3
      AA345   DD546   GG566
      BB456   EE434
      CC657   FD897
  
   关键点是行1,2，3有若干个，不是固定是3个。。

      create table test (a1 varchar2(10), a2 number(3), a3 varchar2(6));
      insert into test values('AA',1,'345');
      insert into test values('BB',1,'456');
      insert into test values('CC',1,'657');
      insert into test values('DD',2,'546');
      insert into test values('EE',2,'434');
      insert into test values('FF',2,'897');
      insert into test values('GG',3,'566');
      insert into test values('EE',1,'457');
      commit;
     
      SQL> select * from test;
     
      A1                 A2 A3
      ---------- ---------- ------
      AA                  1 345
      BB                  1 456
      CC                  1 657
      DD                  2 546
      EE                  2 434
      FF                  2 897
      GG                  3 566
      EE                  1 457
     
      with atest as
       (
       select icol*10+irow icol,irow, decode(icol,1,ivalue) c1 ,decode(icol,2,ivalue) c2, decode(icol,3,ivalue) c3
         from (
           select a2 icol, a1||a3 ivalue, row_number() over(partition by a2 order by a1||a3) irow from test)
       )
     
      select irow , ltrim(rtrim(max(sys_connect_by_path(c1,' ')))) c1,
      ltrim(rtrim(max(sys_connect_by_path(c2,' ')))) c2,
      ltrim(rtrim(max(sys_connect_by_path(c3,' ')))) c3 from atest
      connect by icol-10 = prior icol
      group by irow
     
     
      SQL> col c1 for a10;
      SQL> col c2 for a10;
      SQL> col c3 for a10;
      SQL> select irow , ltrim(rtrim(max(sys_connect_by_path(c1,' ')))) c1,
        2  ltrim(rtrim(max(sys_connect_by_path(c2,' ')))) c2,
        3  ltrim(rtrim(max(sys_connect_by_path(c3,' ')))) c3 from
        4  (
        5   select icol*10+irow icol,irow, decode(icol,1,ivalue) c1 ,decode(icol,2,ivalue) c2, decode(icol,3,ivalue) c3
        6     from (
        7       select a2 icol, a1||a3 ivalue, row_number() over(partition by a2 order by a1||a3) irow from test)
        8   )
        9  connect by icol-10 = prior icol
       10  group by irow
       11  ;
     
            IROW C1         C2         C3
      ---------- ---------- ---------- ----------
               1 AA345      DD546      GG566
               2 BB456      EE434
               3 CC657      FF897
               4 EE457
      

   Oracle10g 中, 还有函数 WMSYS.WM_CONCAT 可以使用, 如

   SQL>                WITH A AS (
           2           SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
           3           SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL  UNION
           4           SELECT 1123 ID,'2008-9-29' DAY,'13:7:47' TIME FROM DUAL  UNION
           5           SELECT 1123 ID,'2008-9-29' DAY,'12:7:47' TIME FROM DUAL )
           6           select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY;
        
                 ID DAY       TIME
         ---------- --------- --------------------
               1123 2008-9-19 19:7:47,9:7:47
               1123 2008-9-29 12:7:47,13:7:47