RE: sql query help please

From: Eric Hutchinson <EricHutchinson_at_comcast.net>
Date: Sat, 30 Apr 2011 19:48:41 -0700
Message-ID: <6007697BDD984161AABC5C05021629D2_at_Ericlaptop>

Try this reference:
<http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9 014.htm#i2125362>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_90 14.htm#i2125362  

You can set up your external file as an external table, and do the whole insert in a single statement, one pass through the table.  

• Eric Hutchinson

  ---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult
Sent: Thursday, April 28, 2011 1:00 PM
To: michaeljmoore_at_gmail.com
Cc: kennethnaim_at_gmail.com; eugene.pipko_at_unionbay.com; oracle-l_at_freelists.org Subject: Re: sql query help please

No, no need for an intermediate table. The multi-table insert (inserting multiple times into the same table, that is) probably does the job. Alternatively

     insert into tableB
     select div,
               cust,
               case n
                   when 1 then jan
                   when 2 then feb
                   when 3 then mar
                end
    from external table
             cross join (select 1 n from dual
                              union all
                              select 2 from dual
                              union all
                              select 3 from dual)

HTH Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on <http://www.youtube.com/user/roughsealtd> Youtube

On 04/28/2011 09:07 PM, Michael Moore wrote:

first load it into a table (a) just as it is currently formated. Then create a table (b) with the new format and load from a to b as follows.

insert into tableB
(select div, cust, jan from tableA
union all
select div, cust, feb from tableA
union all
select div, cust, mar from tableA) ;

Mike

On Thu, Apr 28, 2011 at 11:45 AM, Kenneth Naim <kennethnaim_at_gmail.com> wrote:

Look at multi table inserts. It'll work perfectly for what you need.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eugene Pipko
Sent: Thursday, April 28, 2011 2:42 PM
To: oracle-l_at_freelists.org
Subject: sql query help please  

Hi all,

Oracle 9i on Win2K3.

I am loading data from external file and need to convert it into different format.  

Current format:

---

DIV CUST JAN FEB MAR   New format:

---

DIV CUST JAN DIV CUST FEB DIV CUST MAR   Could you please point me to a reference from where I can learn how to do it?  

Thanks,

Eugene      

---

Checked by AVG - www.avg.com
Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11

---

Checked by AVG - www.avg.com
Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 30 2011 - 21:48:41 CDT