Re: sql query help please

From: Mark Strickland <strickland.mark_at_gmail.com>
Date: Thu, 28 Apr 2011 17:41:36 -0700
Message-ID: <BANLkTi=hQuw3YivuUQs7XRn4=KwLJEdZ_A_at_mail.gmail.com>

Create an External Table for the file, then create the permanent table as select from the external table and use PIVOT to, well, pivot the data.

Mark

On Thu, Apr 28, 2011 at 3:45 PM, Eugene Pipko <eugene.pipko_at_unionbay.com>wrote:

> 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>
> 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 Thu Apr 28 2011 - 19:41:36 CDT