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 >> > >