RE: Nother sql question...

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 3 Mar 2005 12:41:02 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E362B9C78@CWYMSX04.Corp.Acxiom.net>

Steven,

I know your post is old, but I didn't see any responses, so I thought I'd propose the following:

SELECT SUBSTR(string,2)
FROM (

        SELECT SYS_CONNECT_BY_PATH(name,',') string
          FROM (
                SELECT name
                     , LAG(name) OVER (ORDER BY name) prior_name
                  FROM (
                        SELECT DISTINCT name
                          FROM tableA
                         WHERE id IN (<your list of distinct ID values>)
                       )
               )
          START WITH prior_name IS NULL
        CONNECT BY PRIOR name = prior_name
        ORDER BY 1 DESC
       )

WHERE rownum < 2
;

/*
 * Output
 */

SUBSTR(STRING,2)

A,B,C

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

Myths Resolved: "A eye for eye, tooth for tooth, ... (Ex. 21:24) is normally quoted as justification for revenge. On the contrary, it was commanded to stop people from taking greater revenge than the original injustice."

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Stephens, Chris
> Sent: Wednesday, November 10, 2004 1:21 PM
> To: oracle-l_at_freelists.org
> Subject: Nother sql question...
>
>
> I have a list of id's passed in. I need a string of names returned that
> coincide with those id's. It's very easy to just create a cursor, loop
> through, and concantenate to the string on every iteration but I was
> thinking there would be a way to do this in sql with one of the new
> analytic functions but nothing is really jumping out at me.
>
> Anyone?
>
> tableA
> Id Name
> 1 A
> 2 B
> 3 C
>
> Sql>fReturnString(1,2,3);
> Sql>a,b,c
>
>
> --
> http://www.freelists.org/webpage/oracle-l

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 03 2005 - 13:44:21 CST