把多条记录合并成一条记录

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26829342/viewspace-744845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26829342/viewspace-744845/