Re: How to transpose?

From: John Homeyard <homeyard_at_worldnet.att.net>
Date: 1996/05/23
Message-ID: <4o1q2v$c8p_at_mtinsc01-mgt.ops.worldnet.att.net>#1/1

A solution to transposing a table ( flipping it on its side) is offered in the book "Oracle The Complete Reference" by Oracle Press, ISBN 0-07-882097-9. On page 365 in part it states to use (for their example) the sum & decode and put the whole thing in a view. Short example from thir book follows follows :

Quote

      create or replace view client by date as
         select invoicedate,

    sum(decode(clientname, 'ADAH TALBOT', amount,0) adahtalbot,     sum(decode(clientname, 'ELBERT TALBOT,amount,0) elberttalbot,     . . .
    . . .
from invoice
group by invoicedate;

end quote

The invoice table columns are clientname, invoice, amount. a describe on the view created above shows columns of invoicedate and all of the column aliases used to create the view.

John.

Mason Liu <lium_at_dspmp001.research.allied.com> wrote:
>Say I retrieve 8 rows x 95 columns of data. What is the most efficient
>way to transpose it so that it becomes 95 rows x 8 columns.
>
>I can accept a single data block with tab separated data items and
>carriage return+new line separated rows.
>
>Right now, I built a big concatenation SQL to accomplish this and I
>wonder, perhaps, there is a better way. For example, Oracle might have a
>a undocumented TRANSPOSE function for me to use, althought Oracle support
>said none.
>
>Thanks in advance.
>
>Mason Liu
>Object Labs, Inc.
Received on Thu May 23 1996 - 00:00:00 CEST