Solved

Getting DISTINCT values using XMLAGG function

Posted on 2011-02-24
2
6,546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

617 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