Re: sql query help please

I assumed that since the op is loading from an external file, he means that
it is not exposed as an external table. I could be wrong on this assumption,
but if I'm right, then I don't see any simple way around using two tables.

Regards,
Mike


On Thu, Apr 28, 2011 at 12:59 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:

>  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>wrote:
>
>>  Look at multi table inserts. It’ll work perfectly for what you need.
>>
>>
>>
>> *From:* 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
>> *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
>>
>
>

Other related posts: