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 
 ---------- -------------------- --------------------------------------------