使用Oracle数据库自带的函数：wmsys.wm_concat可以解决此问题。

example:select wmsys.wm_concat(t.username) from student t

?

SQL> select version from v$instance;
?
VERSION
-----------------
10.2.0.1.0
?
SQL>
SQL> create table IDTABLE
? 2? (
? 3??? id? number,
? 4??? val varchar2(20)
? 5? )
? 6? ;
?
Table created
?
SQL>
SQL> insert into IDTABLE (ID, VAL)
? 2? values (10, 'abc');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (10, 'abc');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (10, 'def');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (10, 'def');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (20, 'ghi');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (20, 'jkl');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (20, 'mno');
?
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
? 2? values (20, 'mno');
?
1 row inserted
?
SQL> select id,val from idtable;
?
??????? ID VAL
---------- --------------------
??????? 10 abc
??????? 10 abc
??????? 10 def
??????? 10 def
??????? 20 ghi
??????? 20 jkl
??????? 20 mno
??????? 20 mno
?
8 rows selected
?
SQL> commit;
?
Commit complete
?
SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES
? 2??? FROM IDTABLE
? 3?? GROUP BY ID;
?
??????? ID ENAMES
---------- --------------------------------------------
??????? 10 abc,abc,def,def
??????? 20 ghi,jkl,mno,mno
?
SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES
? 2??? FROM IDTABLE
? 3?? GROUP BY ID
? 4?? ORDER BY ID;
?
??????? ID ENAMES
---------- --------------------------------------------
??????? 10 abc,def
??????? 20 ghi,jkl,mno
?
SQL>
SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(PARTITION BY ID) AS ENAMES
? 2??? FROM IDTABLE
? 3?? ORDER BY ID;
?
??????? ID VAL?????????????? ENAMES
---------- -------------------- --------------------------------------------
??????? 10 abc??????????????? abc,abc,def,def
??????? 10 abc??????????????? abc,abc,def,def
??????? 10 def??????????????? abc,abc,def,def
??????? 10 def??????????????? abc,abc,def,def
??????? 20 ghi??????????????? ghi,jkl,mno,mno
??????? 20 jkl???????????????? ghi,jkl,mno,mno
??????? 20 mno?????????????? ghi,jkl,mno,mno
??????? 20 mno?????????????? ghi,jkl,mno,mno
?
8 rows selected
?
SQL>
SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(ORDER BY ID, VAL) AS ENAMES
? 2??? FROM IDTABLE
? 3?? ORDER BY ID;
?
??????? ID VAL?????????????? ENAMES
---------- -------------------- --------------------------------------------
??????? 10 abc??????????????? abc,abc
??????? 10 abc??????????????? abc,abc
??????? 10 def??????????????? abc,abc,def,def
??????? 10 def??????????????? abc,abc,def,def
??????? 20 ghi??????????????? abc,abc,def,def,ghi
??????? 20 jkl???????????????? abc,abc,def,def,ghi,jkl
??????? 20 mno?????????????? abc,abc,def,def,ghi,jkl,mno,mno
??????? 20 mno?????????????? abc,abc,def,def,ghi,jkl,mno,mno
?
8 rows selected