Solved

Getting DISTINCT values using XMLAGG function

Posted on 2011-02-24
2
5,968 Views
Last Modified: 2012-05-11
Trying to obtain distinct values from the following line if SQL code:

rtrim (xmlagg (xmlelement (e, BRAND || ',')).extract ('//text()'), ',') Purchase_BRAND

0
Comment
Question by:globalwm2
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 75 total points
ID: 34972532
wrap your query that returns the BRAND column in an inline view that returns distinct values

something like this...

select
rtrim (xmlagg (xmlelement (e, BRAND || ',')).extract ('//text()'), ',') Purchase_BRAND
from (select distinct brand from yourtable)

0
 

Author Closing Comment

by:globalwm2
ID: 35038838
I ran into issues when trying to perform a CTAS statement to a table. Ultimately has to use the .getclobval() statement and remove the RTRIM of the ',' to move on.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question