RE: sql query help please

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
Date: Thu, 28 Apr 2011 15:45:33 -0700
Message-ID: <34DB87F47199374280ADFD2968CDBCFA887A78FB67_at_MAIL01KT.seattlepacificindustries.com>

Thank you Stephane and Mike

From: Stephane Faroult [mailto:sfaroult_at_roughsea.com] Sent: Thursday, April 28, 2011 1:00 PM
To: michaeljmoore_at_gmail.com
Cc: kennethnaim_at_gmail.com; Eugene Pipko; 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 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_at_gmail.com<mailto: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> [mailto: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<mailto: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<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

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

Received on Thu Apr 28 2011 - 17:45:33 CDT