Oracle-L: Re: SQL Query

From: Mike Spalinger <Michael.Spalinger_at_Sun.COM>
Date: Thu, 25 Sep 2003 09:04:53 -0800
Message-ID: <F001.005D10F1.20030925090453@fatcity.com>

Imran,

Tom Kyte has a thread that might help:
http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,

Essentially, you can do this:

CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )

RETURN VARCHAR2 IS

     ret VARCHAR2(32000);
     tmp VARCHAR2(4000);
BEGIN
     loop
         fetch cur into tmp;
         exit when cur%NOTFOUND;
             ret := ret || ' ' || tmp;
     end loop;
     RETURN ret;

END;
/

SQL> select * from mike;

ID TEXT ORDR
---------- ---------- ----------

          1 B                   2
          1 A                   1
          1 C                   3

SELECT id, SUBSTR(CONCAT_LIST(CURSOR(

SELECT text

       FROM (select id, text, ordr from mike order by ordr) ee
       WHERE e.id = ee.id)),1,40) list

FROM ( select distinct id from mike) e;

ID LIST

---------- --------------------
          1  A B C

Mike

Imran Ashraf wrote:
> Hi,
>
> I have the following data , table Temp
>
> ID Text Order
> 1 B 2
> 1 A 1
> 1 C 3
>
>
> I want to write a query which says: wherever there is more than 1 occurrence
> of ID then concatenate the text in the order specified in the order column.
> So i would get:
>
> ID Text
> 1 A B C
>
> Any suggestions?
>
> Thanks
>
> Imran

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mike Spalinger
  INET: Michael.Spalinger_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Sep 25 2003 - 12:04:53 CDT