Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

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

Highlighted
Four Stars

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

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.

Highlighted
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog