Re: Return Single Row with Multiple Cols Instead of Multiple Row

From: Billy <vslabs_at_onwe.co.za>
Date: 26 Oct 2005 22:52:56 -0700
Message-ID: <1130392376.080364.42120@g49g2000cwa.googlegroups.com>

Anil G wrote:

> Thank you for responses, the way i need output is single line
> ABC Motorcycle On Road Casual ZX124 Red CA-Emision
>
> It is tough as the model returns those as multiple rows, cause this is
> just the small section of SQL, actual output is giantic, it;s like
> Total Orders, Forecasts etc by each of above column values.

The problem is that one cannot define a SQL projection with variable columns. The number of columns projected (returned) must be known up front.

So there are two basic choices here. Return a collection as a column. One column is returned, but it itself is a collection array (ADT or Advance Data Type).

The problem with this is that the client will need to know how to deal with an ADT column.

The second choice is to simply string the rows together into a single VARCHAR2 column. Easily done, but then you are limited to a VARCHAR2(4000) only. With an ATD there is not real limitation wrt the number of items in the collection.

The code below illustrates the second method. A single VARCHAR2 is returned, stringing all the rows (up to a maximum of a 100), together. Note that the REF CURSOR is expected to also only contain a single column - thus concat the columns in the REF CURSOR if needed.

• collection used to grab the rows from the REF CURSOR and buffer it SQL> create or replace type TStrings is table of varchar2(4000); 1 /

Type created.

• definition of the column we will return SQL> create or replace type TSingleColumn is object 2 ( 3 col varchar2(4000) 4 ); 5 /

Type created.

• definition of the table we will return SQL> create or replace type TSingleColumnTable is table of TSingleColumn; 2 /

Type created.

• ref cursor type definition SQL> create or replace package lib is 2 type TRefCursor is REF CURSOR; 3 end; 4 /

Package created.

• pipeline table which takes a ref cursor as input and returns a single col row as result SQL> create or replace function pipe1( c lib.TRefCursor ) return TSingleColumnTable pipelined is 2 s TStrings; 3 i integer; 4 col TSingleColumn; 5 begin 6 col := new TSingleColumn( null ); 7 fetch c bulk collect into s limit 100; 8 9 for i in 1..s.Count 10 loop 11 col.col := col.col || s(i) || ' '; 12 end loop; 13 14 pipe row( col ); 15 close c; 16 return; 17 end; 18 /

Function created.

SQL> select object_name from all_objects where rownum < 11;

OBJECT_NAME

---

/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder
/10501902_BasicFileChooserUINe
/105072e7_HttpSessionBindingEv
/106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle
/10744837_ObjectStreamClass2

10 rows selected.

SQL> select * from TABLE( pipe1( CURSOR(select object_name from all_objects where rownum < 11) ) );

COL

---

/1005bd30_LnkdConstant /10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList /103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder /10501902_BasicFileChoos
erUINe /105072e7_HttpSessionBindingEv /106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle /10744837_ObjectStreamClass2

.. and this returns a single row with a single column containing 10 rows' data.

This should give you some ideas on how to deal with your requirement in SQL. Keep in mind though that formatting is best dealt within the client - it is after all responsible for the presentation layer. So just to what extent formatting should be done at the SQL (and PL/SQL) level, must be considered.

--
Billy

Received on Thu Oct 27 2005 - 00:52:56 CDT