aggregate column of nvarchar2 and obtain result in nvarchar2 too
As the documentation describes:


  The return data type is RAW if the measure column is RAW; otherwise
  the return value is VARCHAR2.


So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion.

LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert everything to RAW, then convert the result back to NVARCHAR2.

Below is an example:

SQL&gt; select parameter, value from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               EE8ISO8859P2
NLS_NCHAR_CHARACTERSET         AL16UTF16


My default characterset does not support the € sign.

SQL&gt; create table t1 (id number, c1 varchar2(20), c2 nvarchar2(20));

Table created.

SQL&gt; insert into t1 values (1, 'A', 'A');

1 row created.

SQL&gt; insert into t1 values (2,  NCHR(8364),  NCHR(8364));

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; select * from t1;

        ID C1 C2
---------- -- --
         1 A  A
         2 ?  €


As you can see, i get a ? instead of it using VARCHAR2.

If I simply try to use LISTAGG on the NVARCHAR2 column, data will be lost during conversion:

select
  listagg(c2, ',') within group (order by id) as list1
from t1;

LIST1
----------
 A, Ź


If I try to cast the result, still not good enough, as data was lost during the conversion to VARCHAR2:

select
  cast(listagg(c2, ',') within group (order by id) as nvarchar2(20)) as list2
from t1;

LIST2
----------
 A, Ź


The remaining option is, to convert to RAW:

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(',')) within group (order by id)) as list3
from t1;

LIST3
----------
AⰠ


Converting a VARCHAR2 delimiter will mess up the data, I need to specify the delimiter also in NVARCHAR2, so instead of ',', I have to use N',':

select
  utl_raw.cast_to_nvarchar2(listagg(utl_raw.cast_to_raw(c2), utl_raw.cast_to_raw(N',')) within group (order by id)) as list4
from t1;

LIST4
----------
A,€