Re: Distinct string statement

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 21 Feb 2005 23:39:26 GMT
Message-ID: <OiuSd.8872$DC6.5505@newssvr14.news.prodigy.com>

DA Morgan wrote:

> Marina S. wrote:
> 

>> Hi,
>>
>> I have some question,
>>
>> I have table with 2 columns
>>
>> id symbol
>>
>> 11 abc,bbc,abf,cde
>> 22 rda,abc,aig
>> 33 ibm,aig,sun
>> 55 aig,klm,suk,sun
>> 23 bbc,kln,sun,adf
>> .. ..
>> .. ..
>>
>>
>> I need to select distinct symbol strings from symbol column,they
>> shoudn't be repeated,but should be comma separated as they appear in a
>> column.
>> the result to user should be like:
>> abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...
>>
>> Any ideas are really appreciated,
>>
>> Marina

> 
> 
> Given that this is homework you will need to do a lot more than this
> to obtain help. Show us your work and we will hint you toward the
> solution.
> 
> PS: The Oracle version is essential for help.

Well, I wouldn't want to do the OP's presumed homework ;-) but I got to thinking how simple this would be in Perl, and not being a PL/SQL jockey, I wondered how to do it in that language.

My very first search hit (left as an exercise to the alleged student) got me to a pair of nice PL/SQL functions (SPLIT and JOIN) written by Scott Stephens of Oracle. As he says, "Many scripting languages, such as Perl and Python, provide functions that do this with their own language-specific list of values; so it's surprising that, as of yet, this functionality isn't a standard part of SQL functions."

select * from t;

         ID SYMBOL

---------- --------------------
         11 abc,bbc,abf,cde
         22 rda,abc,aig
         33 ibm,aig,sun
         55 aig,klm,suk,sun
         23 bbc,kln,sun,adf

select join(cursor(
         select distinct column_value
         from table(split(join(cursor(select symbol from t))))))

from dual;

JOIN(CURSOR(SELECTDISTINCTCOLUMN_VALUEFROMTABLE(SPLIT(JOIN(CURSOR(SELECTSYMBOLFROMT))))))

---

abc,abf,adf,aig,bbc,cde,ibm,klm,kln,rda,suk,sun

This won't quite meet the OP's requirements as the result is sorted normally not topologically.

-Mark Bole Received on Mon Feb 21 2005 - 17:39:26 CST