Re: sql query help please

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 28 Apr 2011 21:59:44 +0200
Message-ID: <4DB9C730.8050709_at_roughsea.com>



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 - 14:59:44 CDT

Original text of this message