How to avoid duplicate value using XMLAGG, XMLELEMENT tags

See more:

SQL

Oracle

I want to concatenate two columns value in to a single column based on it's id.
So i have tried the bellow code -

RTRIM(XMLAGG(XMLELEMENT(E,EMP_NAME,', ').EXTRACT
('//text()')
  ORDER BY EMP_NAME).GetClobVal(),',') EMP_NAME

But it is showing the data multiple times. Please suggest a way to resolve it.

Example - table name EMP having columns -(EMP_ID, EMP_NAME) values - ('1', 'Sourav Ganguly'),('1', 'Sachin Tendulkar'). The table is not having such a column as primary key.

expected Output -

EMP_NAME

Sourav Ganguly, Sachin Tendulkar - in a single row.

But Output is coming as -

EMP_NAME

Sourav Ganguly, Sourav Ganguly, Sourav Ganguly, Sourav Ganguly, Sourav Ganguly, Sachin Tendulkar,Sachin Tendulkar, Sachin Tendulkar, Sachin Tendulkar, Sachin Tendulkar.


Please help me to avoid the duplicacy.

Thanks in Advance.