SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Duplicates in XMLAGG XMLELEMENT

User_HQFVTJun 27 2017 — edited Jul 7 2017

Hello Folks,

I am using the below xmlagg function in one of my oracle queries to list the distinct values.

RTRIM(XMLAGG(XMLELEMENT(E,column_name,',').EXTRACT('//text()')

ORDER BY column_name).GetClobVal(),',') as column_name

I am getting the out put but it's include the duplicate values which I am looking for.

In my case I cant' use the distinct with group by in inner query and pass the values to outer query as it has more attribute in select clause.

Input

Clmn1          clmn2
                     LO-
                    TK-
                     100115
AAA             100115

Output :

clmn1 clmn2
AAA 100115,100115,LO-,TK

Expected out put:

clmn1 clmn2

AAA 100115,LO-,TK

Can you please help to remove dups?

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 4 2017

Added on Jun 27 2017

4 comments

2,964 views