How to convert multiple rows into single row in iformatica for large volume of data, need best solution

Question

I have data in table A as below

Assetid attribute value
1546    Ins_date   05062011
1546    status     active
1546    X          10.4567
1546    Y          27.56
1546    size       17
675     X          4.778
675     Y          53.676
675     depth      5
675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

Answer 1

As suggested in previous answer you can use the aggregator. Since your data set is large you can use a technique using variable port in an expression as well provided the data is sorted before it reaches the expression.

You can download the sample mappings that demonstrate both the the techniques from Informatica Marketplace App titled "PowerCenter Mapping: Convert Rows Into Columns".

Answer 2

Use an Aggregator transformation to condense the records into one record per assetid. Then for each attribute, create a port that returns MAX(value) where the attribute matches. Note that this method assumes that you know all possible attributes ahead of time.

Answer 3

Use a router to split the rows into separate groups depending on attribute and then use a set of joiners to merge the rows with the same assetid values.