SQL & PL/SQL

Hi,

Oracle Version Installed is 11.0.2r6. I have a table dummy with data like below:

STID ADDRESS File_Type
-------- ------------------ ----------------
9927a0 ABC SALES
9927b0 XYZ TRANSFER
9927a0 ABC TRANSFER
9927b0 XYZ TRANSFER

I want the Output as:

STID ADDRESS File_Type
-------- ------------------ ----------------
9927a0 ABC SALES, TRANSFER
9927b0 XYZ TRANSFER

I had used 2 methods to achieve able result, but facing some Issues.

Method-1 :
--------------
SELECT STID, ADDRESS, LISTAGG(File_Type, ',') WITHIN GROUP (ORDER BY File_Type) AS file_type
FROM DUMMY
GROUP BY STID, ADDRESS;

I got the output as :

STID ADDRESS File_Type
-------- ------------------ ----------------
9927a0 ABC SALES, TRANSFER
9927b0 XYZ TRANSFER, TRANSFER

Method-2:
-------------
SELECT STID,
ADDRESS,
CAST(COLLECT(DISTINCT File_Type) AS SYS.ODCIVARCHAR2LIST) AS File_Type
FROM (SELECT STID, ADDRESS, File_Type FROM DUMMY)
GROUP BY STID, ADDRESS;

I got the output as:

STID ADDRESS File_Type
-------- ------------------ ----------------
9927a0 ABC SYS.ODCIVARCHAR2LIST(SALES, TRANSFER)
9927b0 XYZ SYS.ODCIVARCHAR2LIST(TRANSFER)

Even method 2 gave proper result which the way I want, "SYS.ODCIVARCHAR2LIST" is coming as prefix in the result. I tried using replace function, but it is not working. I tried to use "wm_concat" method, but this is not working.

Can anyone please help me in achieving this? I want simple Select statement, not the functions/procedures etc.

Thanks,
Sekhar.