Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: reverse pivoting -- more complex situation

Re: reverse pivoting -- more complex situation

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 13 Mar 2002 07:09:23 GMT
Message-ID: <3c8ee78c.2799163@news.saix.net>


"Kenny Yu" <kyu_at_biodiscovery.com> wrote:

>A more complex situation, --and this is not a homework assignment, -- is
>where the number of quarters are not known, or arbitrarily defined for a
>year.

This is only possible with dynamic SQL. It is not possible to write a single SQL statement that can create a result set of variable columns.

You need programatic intervention in order to determine the number of columns up front, for constructing the SQL statement.

>I am ready to work in PL/SQL to generate a dynamic SQL to create a view. I
>generally believe that everything can be done in one single statement, but
>am challenged here.

PL/SQL with dynamic SQL is a solution. I also do not think this is a failure of SQL itself. SQL needs to deal with a known data set - what you have here is a totally unknown data set where a row can have variables columns.

As an alternative, you can also do this on the client side - using the argument that as the client deals with the representation of data to the user, it can also deal with this unique variable pivot requirement. In fact, this would be pretty easy to do - subclassing a normal grid and writing the pivot logic into the new class (using something like Delphi).

--
Billy
Received on Wed Mar 13 2002 - 01:09:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US