Oracle wmsys. wm_concat function usage

Oracle wmsys. wm_concat function, which uses the ',' link character.

Example:

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
------------------------------------------------------------------------------------------
10 AB, BC, CD
10 AB, BC, CD
10 AB, BC, CD
20 AB, BC, CD, hi, IJ, Mn
20 AB, BC, CD, hi, IJ, Mn
20 AB, BC, CD, hi, IJ, Mn

6 rows selected

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

ID name
------------------------------------------------------------------------------------------
10 AB
10 AB, BC
10 AB, BC, CD
20 AB, BC, CD, hi
20 AB, BC, CD, hi, IJ
20 AB, BC, CD, hi, IJ, Mn

6 rows selected

I personally think this usage is interesting.

SQL> select ID, wmsys. wm_concat (name) over (partition by ID) name from idtable;

ID name
------------------------------------------------------------------------------------------
10 AB, BC, CD
10 AB, BC, CD
10 AB, BC, CD
20 hi, IJ, Mn
20 hi, IJ, Mn
20 hi, IJ, Mn

6 rows selected

SQL> select ID, wmsys. wm_concat (name) over (partition by ID, name) name from idtable;

ID name
------------------------------------------------------------------------------------------
10 AB
10 BC
10 cd
20 hi
20 IJ
20 mN

6 rows selected