Re: reverse pivoting -- more complex situation

"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