How To transpose multi field data

[#1]

Hi,

I have a table with multiple addresses listed for a business. So I have a mailing address and physical address

Lets say the layout is:

BizId, PAddL1, PAddl2,Pcity,PState,PZip, MAddL1, MAddl2,Mcity,MState,MZip

How can I transpose this while loading so I can get this structure:

BizId, AddType, AddL1, Addl2,city,State,Zip

Thanks.

-Sachin

[#2]

Hi

You can read your file twice like this

Load

BizId,

PAddL1 as Addl1,

PAddl2 as Addl2,

Pcity as City,

PState as State,

PZip as Zip,

'Physical' as AddType

from yourDataFile ;

Concatenate Load

BizId,

MAddL1 as Addl1,

MAddl2 as Addl2,

Mcity as City,

MState as State,

MZip as Zip,

'Mailing' as AddType

from yourDataFile ;

JJ

[#3]

I have 3.5 millions records and several fields like this in each record. This works for now, but the load is pretty slow as I am having to parse the file 4 times, with as many as 15 concat statements.

Working fine for now. But wish there was a more elegant solution.

Thanks.

[CELAMBARASAN · Accepted solution]

Hi,

     Instead of loading multiple times from data file you can do like this by altering the way Jean-Jacques Jesua suggestion to.

TempTable:

Load

          BizId,

          PAddL1,

          PAddl2,

          Pcity,

          PState,

          PZip,

          MAddL1,

          MAddl2,

          Mcity,

          MState,

          MZip

from datafile;

MainTable:

Load

BizId,

PAddL1 as Addl1,

PAddl2 as Addl2,

Pcity as City,

PState as State,

PZip as Zip,

'Physical' as AddType

Resident

      TempTable;

Concatenate Load

BizId,

MAddL1 as Addl1,

MAddl2 as Addl2,

Mcity as City,

MState as State,

MZip as Zip,

'Mailing' as AddType

Resident

     TempTable;

Drop Table TempTable;

it reduces some more time.

Celambarasan

[#5]

This seems a better approach!! Thanks.