Re: Transposing Table Columns to Table Rows

In article <james.lawrence.30.000E5BC5_at_EPAMAIL.EPA.GOV>, james.lawrence_at_EPAMAIL.EPA.GOV says:
>
>In article <3onu98$as4_at_News1.mcs.com> Michael Janiak <quakerdw_at_mcs.com> writes:
>>From: Michael Janiak <quakerdw_at_mcs.com>
>>Subject: Transposing Table Columns to Table Rows
>>Date: 9 May 1995 14:30:00 GMT
 

>>I have a Powerbuilder application that needs to read an Oracle table
>>and insert the values in that table's columns into another table as
>>rows. For example: We select the values in col1, col2, and col3 from
>>table A. These values need to be inserted into col1 of rows r1,r2,
>>and r3 of table B.
 

>>At the present time the application programmer is performing separate
>>insert subselects. There can be up to 30 columns selected.
 

>>Does anyone know of a better way of doing this?
 

>>Thank you.
>
>How about a union?
>
>insert into tableb (col1)
>select col1 from tablea
>union
>select col2 from tablea
>union
>select col3 from tablea;

In "Oracle 7 - The Complete Reference" by George Koch and Robert Muller - Chapter 15, there is a section on "Flipping a Table onto Its Side", p.367-369.

Decode is used in this case to create a view, but you could probably do something similar in a query feeding into an insert. I don't know enough about your app to know if this will do everything you're looking for, but if you can find the reference, it might be worth a shot.

Hope this helps!

Paul Received on Fri May 19 1995 - 00:00:00 CEST