converting 100s of rows into 100s of columns

[bmadhav]

Hi
I know a lot of pivot (vertical and horizontal) stuff has been covered on DSX, but, i some how could not find any optimal soln for what i am trying to accomplish.
We have a situation where we have several hundreds of rows for a given customer and all these rows need to be transformed into one unique row per customer.
The target table (Customer table), which we are building consists of approx 500 columns at the customer level.
CustomerA could have 200 rows of informtion, Customer B could have 100 rows of info, Customer C could have 500 rows of information and so on.
sample data:

C_no attribute value
==== ===== ====
C100 A1 "NAME"
C100 A2 100.00
C100 A4 8
C200 A1 "NAME1"
C200 A3 "Y"
C200 A5 -15


O/P TABLE
==========
C_no A1 A2 A3 A4 A5 ..... A500
====== === == == == === ======
C100 NAME 100.00 8
C200 NAME1 Y -15


Should i have to do a basic routine to handle something as large as this or can i do it all in a transformer?

[ArndW]

Create a hashed file with just two columns, "CustNo" and "Data". Do a lookup on the customer number for each row in your input file. Use one of the substring functions (INDEX,LOCATE,MATCHES,etc.) to see if your attribute text is in the data portion. If not, write the record back with the new attribute appended to the end of the data..

When the job is finished you can take your hashed file and write it out to a sequential with whatever formatting you would like.

[bmadhav]

Thanks for the response!
The solution u provided ensures i get all the data for a given customer on a single row, but, how do i make sure i map the correct attribute to the corresponding value of the attribute.
Since each attribute is a column on my o/p, if the incoming data does not have a row for an attribute, then the o/p column shd be blank for that attribute.
In the example C100 customer shd have blanks in columns A3 & A5.

[kcbland]

If you have a fixed number of reference columns, then switch your hashed reference file to have the same number of columns. The derivation for each column is the same except for the offset value. Something like:

If inlink.YourAttributeIndex = 1 Then inlink.YourDataValue Else reflink.DataValue.

For reference link columns 1 to 500 just change the "= 1" to each value to 500.

When your job is done, spool the hash file out.

[bmadhav]

Thanks ken! I like this approach, this also ensures i will not overwrite the 1st attribute with a different value attribute.
Just to confirm, i need to read and write into the same hash file, correct?

[ray.wurlod]

There are other techniques too. Search the forum for VERTICAL PIVOT.

[kcbland]

Just to confirm, i need to read and write into the same hash file, correct?

Yes, which means you need to have this file created in advance of referencing it.