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

wmsys oracle10下的系统用户

=====================================================================

WMSYS.WM_CONCAT 用法与STRAGG 函数相似

=====================================================================

CREATE TABLE Tmp_1
(brand_code VARCHAR2(6),
 brand_name VARCHAR2(20)
);

INSERT INTO Tmp_1(brand_code,brand_name)
VALUES('三星','S7520U');
INSERT INTO Tmp_1(brand_code,brand_name)
VALUES('三星','S8000c');
INSERT INTO Tmp_1(brand_code,brand_name)
VALUES('三星','SCH-W709 ');

INSERT INTO Tmp_1(brand_code,brand_name)
VALUES('诺基亚','N97');

INSERT INTO Tmp_1(brand_code,brand_name)
VALUES('诺基亚','E71');
COMMIT;

SQL> SELECT BRAND_CODE, STRAGG(BRAND_NAME) AS STRING, COUNT(0) TOTAL
  2    FROM TMP_1
  3   GROUP BY BRAND_CODE;
 
BRAND_CODE STRING                                                                            TOTAL
---------- -------------------------------------------------------------------------------- ----------
诺基亚  N97,E71                                                                              2
三星     S7520U,S8000c,SCH-W709                                                    3
 
SQL>
SQL> SELECT BRAND_CODE, WMSYS.WM_CONCAT(BRAND_NAME) AS ENAMES, COUNT(0) TOTAL
  2    FROM TMP_1
  3   GROUP BY BRAND_CODE;
 
BRAND_CODE ENAMES                                                                           TOTAL
---------- -------------------------------------------------------------------------------- ----------
诺基亚  N97,E71                                                                              2
三星     S7520U,S8000c,SCH-W709                                                    3