Re: Transposing Table Columns to Table Rows

From: Don Vick <dvick_at_lanier.com>
Date: 1995/05/09
Message-ID: <D8Bpnr.G7v_at_lanier.com>#1/1

In article <3onu98$as4_at_news1.mcs.com>, Michael Janiak <quakerdw_at_mcs.com> wrote:
>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.
>

I don't know if you can translate this to Powerbuilder, but the following seems to work in SQL*Plus:

---

rem Insert columns of a select into separate rows of another table.

Create a table for testing:
drop table test_table;
create table test_table as

   select 1 col1, 2 col2, 3 col3
   from dual;
insert into test_table values (5, 6, 7);

rem Generate SQL statements from the contents of test_table: set echo off verify off feedback off heading off pagesize 0 termout off spool /tmp/tpose.sql

select 'insert into other.table values (' || col1 || ');' ,
       'insert into other.table values (' || col2 || ');' ,
       'insert into other.table values (' || col3 || ');' 

from test_table;
spool off
start /tmp/tpose.sql
host rm /tmp/tpose.sql
exit

---

Looks like a kludge, but maybe it will give you an idea :-)

---

Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Tue May 09 1995 - 00:00:00 CEST