Solved

Getting DISTINCT values using XMLAGG function

Posted on 2011-02-24
2
5,823 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 80
How to simplify my SQL statement? 14 50
SQL query bug 3 28
SQL join help to a thrid table 51 75
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now