Re: sql query help please

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: michaeljmoore@xxxxxxxxx
  • Date: Thu, 28 Apr 2011 21:59:44 +0200

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 Youtube <http://www.youtube.com/user/roughsealtd>

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@xxxxxxxxx <mailto:kennethnaim@xxxxxxxxx>> wrote: 

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

    *From:*oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
    [mailto:oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Eugene Pipko
    *Sent:* Thursday, April 28, 2011 2:42 PM
    *To:* oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
    *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 <http://www.avg.com>
    Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date:
    04/28/11

    ------------------------------------------------------------------------


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2011