albert's blog: [Oracle] LISTAGG cannot handle NVARCHAR2

2015/08/17

[Oracle] LISTAGG cannot handle NVARCHAR2

Problem
Assume this following SQL statement will get agency name

SELECT age, agency
FROM ave011fa
WHERE fyr='104'
AND age IN ('0240005', '0000000', '2971021');

The search result is as bellows:

If we would like to concatenate multiple agencies into one row, we need to make good use of LISTAGG function.

Therefore, the SQL statement will modify as following:

SELECT fyr,
       LISTAGG(AGENCY, ';') WITHIN GROUP (ORDER BY fyr) AGENCY
FROM ave011fa
WHERE fyr='104'
AND age IN ('0240005', '0000000', '2971021')
GROUP BY fyr;

But the agency name's characters are broken

Solution
Owing to agency column's data type is NVARCHAR2, LISTAGG function cannot handle NVARCHAR2 properly

So you need to cast agency to a char to fix this problem

SELECT fyr,
        LISTAGG(TO_CHAR(AGENCY), ';') WITHIN GROUP (ORDER BY fyr) AGENCY
FROM ave011fa
WHERE fyr='104'
AND age IN ('0240005', '0000000', '2971021')
GROUP BY fyr