Re: Interesting data transposition problem

From: Matthias Kleinicke <Matthias.Kleinicke_at_gmx.de>
Date: Fri, 10 Jun 2005 23:39:37 +0200
Message-ID: <42aa0762$0$50768$892e7fe2@authen.white.readfreenews.net>

puneet.bansal_at_wipro.com wrote:
> But I am using Oracle 8i. So what's the alternative?

there is no direct pivot in oracle 8i.

There are some possible solution:

1.
In oracle You could use a PL/SQL-routine selecting the data and performing the requested operation. This way you could also deal with too many columns.

2.
For any given number of columns You could use analytic functions to optimize the statement (I guess order of values should be ascending):

select max(decode(col1, 'A', col2, null)) A

       ,max(decode(col1, 'B', col2, null)) B
        ...
from (select rank() over (partition by col1 order by col2) r,
              col1,
              col2
         from ttt)

group by r order by r

hope syntax fits as I didn't have database running here ;-)

this statement could be used for any number of columns.

You could also use an second select to determine an order for the values of col1:
select rank() over (order by col1) pos, col1 group by col1

if You join this in You could replace the constants inside of decode whith the relative position in the order, so You could take eg. the first 10 col-values.

3.
If You migrate to 9i there are table-functions. This function can act like a table. I didn't try myself, but maybe You can write an pivot yourself.

But what do you need a result with unpredictible number of columns for? I suppose one of the above solutions can be used (preferable 2. with the order integrated).

Matthias Received on Fri Jun 10 2005 - 16:39:37 CDT