Home » SQL & PL/SQL » SQL & PL/SQL » Append multiple results to single row
Append multiple results to single row [message #266330] Mon, 10 September 2007 09:59 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Oracle 10.2 RHEL4

I have the below query:
select sc.supercategoryname from websiteandcategories wac, SUPERCATEGORY sc where wac.supercategoryid = sc.supercategoryid and wac.websiteid= 13; 


that returns the below result:
SUPERCATEGORYNAME
--------------------------------------------------
Home & Garden: Furniture
Home & Garden: Bed & Bath
Retail: Apparel Stores


I would like these three rows combined into a single row with a comma delimitor addedd so that it looks like this:
SUPERCATEGORYNAME
--------------------------------------------------
Home & Garden: Furniture, Home & Garden: Bed & Bath, Retail: Apparel Stores


Is this possible?

Thanks.

[Updated on: Mon, 10 September 2007 10:01]

Report message to a moderator

Re: Append multiple results to single row [message #266333 is a reply to message #266330] Mon, 10 September 2007 10:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is a user-defined stored function "stragg" around that does this.

Have a look here for an example :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#18313422264397
icon14.gif  Re: Append multiple results to single row [message #266334 is a reply to message #266333] Mon, 10 September 2007 10:19 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

I think this will help you.
ROWTOCOLUMN
Cheers
Soumen
Re: Append multiple results to single row [message #266356 is a reply to message #266334] Mon, 10 September 2007 11:25 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
That was perfect. Just what I was looking for! Thanks for your assistance!
Re: Append multiple results to single row [message #267550 is a reply to message #266356] Thu, 13 September 2007 12:43 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Currently, the default delimiter is a '. How can I define another delimiter besides the default?
Thanks.
Re: Append multiple results to single row [message #267578 is a reply to message #267550] Thu, 13 September 2007 14:00 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
nevermind, it's pretty obvious where the delimiter is set:


CREATE OR REPLACE 
FUNCTION rowtocol( p_slct IN VARCHAR2,
p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2 
AUTHID CURRENT_USER AS
/*


1) Column should be character type.
2) If it is non-character type, column has to be converted into character type.
3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument.



     TYPE c_refcur IS REF CURSOR;
     lc_str VARCHAR2(4000);
     lc_colval VARCHAR2(4000);
     c_dummy c_refcur;
     l number;
     BEGIN
     OPEN c_dummy FOR p_slct;
     LOOP
     FETCH c_dummy INTO lc_colval;
     EXIT WHEN c_dummy%NOTFOUND;
     lc_str := lc_str || p_dlmtr || lc_colval;
     END LOOP;
     CLOSE c_dummy;
     RETURN SUBSTR(lc_str,2);
     /* 
     EXCEPTION 
     WHEN OTHERS THEN
     lc_str := SQLERRM;
     IF c_dummy%ISOPEN THEN
     CLOSE c_dummy;
     END IF;
     RETURN lc_str;
     */
     END;
     /
Previous Topic: datafile max size
Next Topic: Ref Cursor in DB-Link
Goto Forum:
  


Current Time: Fri Dec 02 20:56:45 CST 2016

Total time taken to generate the page: 0.14766 seconds