how to convert to pivot table when the number of columns can change

[Anonymous]

i have a table which looks like this

ID  Product  Sale
1    A1       40
2    A2       30
..
n    An       50

i want to use Talend job to create a pivot table like this:

ID  A1    A2   ..  An
1   40    null    null
2   null  30      null
n   null  null    50

my Talend Job looks like this:

Tmssql_input ==> Tdenomalized ==>TextractedDemilitedFields==> Tmssql_ouput.

(i learn it from this Post: https://community.talend.com/t5/Design-and-Development/resolved-Pivot-Table-with-multiple-rows/td-p/...)

The Problem with the Job is whenever there is a new Product, i need to modify the Schema of TextractedDemilitedFields with new Product-column manually.

therefore i want to change the Job to make it dynamic. the Job should automatic pivot the new Product when it comes.

i can't use tpivottocolumndelimited because my data has 1 billion records and this tpivot components works very slowly

Can you please help me with this Issue?

Thankyou.

[Anonymous]

Hi phalondon
You are right. You need to define the schema at design time if you use tExtractDelimitedFields. For a big volume of data set, you can do a loop and query a certain amount of data each time. For example:
tLoop--iterate--tMssqlInput--tPivotxxx-->tMssqlOuput.

on tMssqlinput, add a filter condition in the query:
"select * from table_name where ID>="+((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))+" and ID<="+((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))+the Step value on tLoop

Regards
Shong