Solved

Getting DISTINCT values using XMLAGG function

Posted on 2011-02-24

1 solution

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

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

Join The Community

2 Comments

LVL 74

Accepted Solution

by:sdstuber

sdstuber earned 75 total points

ID: 349725322011-02-24

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)

Author Closing Comment

by:globalwm2

ID: 350388382011-03-04

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.

Featured Post

Question has a verified solution.

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

The Lone Date Ranger :: there is just one right way

Article by: PortletPaul

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. …

Query Syntax

Export Table Data in SQL Server Management Studio

Article by: Dirk

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.

Connecting To SQL Server From Oracle Using Heterogeneous Services Generic Gateway

Video by: Steve

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.