1、第一种

first implementation simply uses static sql to select all of the values for B from
T for a given A and string them together:

函数代码：

CREATE OR REPLACE FUNCTION get_transposed (p_a IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_str   VARCHAR2 (2000) DEFAULT NULL;
   l_sep   VARCHAR2 (1) DEFAULT NULL;
BEGIN
   FOR x IN (SELECT b
               FROM t
              WHERE a = p_a)
   LOOP
      l_str := l_str || l_sep || x.b;
      l_sep := '-';
   END LOOP;

   RETURN l_str;
END;
/

?

样例：

column t format a30;

drop table t;


create table t
( a varchar2(25),
  b varchar2(25)
);

insert into t values ( '210','5000' );
insert into t values ( '210','5001' );
insert into t values ( '210','5002' );
insert into t values ( '220','6001' );
insert into t values ( '220','6002' );
commit;

?

select a, get_transposed( a ) t
from t
group by a
/

A                         T
------------------------- ------------------------------
210                       5000-5001-5002
220                       6001-6002

?

2、第二种

next example is more complex.? We will pass in the name of the 'key' column (the
column to pivot on), a value for that column, the name of the column to actually select
out and string together and finally the table to select from:

函数代码：

create or replace
function transpose( p_key_name in varchar2,
                    p_key_val  in varchar2,
                    p_other_col_name in varchar2,
                    p_tname     in varchar2 )
return varchar2
as
    type rc is ref cursor;
    l_str    varchar2(4000);
    l_sep    varchar2(1);
    l_val    varchar2(4000);

    l_cur    rc;
begin

    open l_cur for 'select '||p_other_col_name||'
                      from '|| p_tname || '
                     where ' || p_key_name || ' = :x '
                using p_key_val;

    loop
        fetch l_cur into l_val;
        exit when l_cur%notfound;
        l_str := l_str || l_sep || l_val;
        l_sep := '-';
    end loop;
    close l_cur;

    return l_str;
end;
/

?使用样例：

REM List the values of "B" for a given value
REM of "A" in the table "T"

select a, transpose( 'a', a, 'b', 't' ) t
  from t
 group by a
/

A                         T
------------------------- ------------------------------
210                       5000-5001-5002
220                       6001-6002

?

3、第三种

直接使用wm_sys.wm_concat，它可以使用“,”来连接字符串

参考样例：http://blog.csdn.net/yy_mm_dd/article/details/3182953

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted
SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

        ID NAME
---------- ------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn

6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
  2  group by id;

        ID NAME
---------- --------------------------------------------
        10 ab,bc,cd
        20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

        ID NAME
---------- -------------