Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
RVeitch_84
Creator
Creator

Looping through multiple columns Tmap

I have a table with over 89 columns of "meter types", see example below.  I have to read each meter type and populate it in another table along with a meter type definition.

Record Type Location Seq Number Date/Time Serial # Station # meter1 meter2 meter3
4 abc 1 1/24/2024 1234 11 5    
4 abc 2 1/24/2024 1234     10  
4 abc 3 1/24/2024 1234       25

 

 

I have to query that table and populate the data in a different format where 'END_VALUE' is the value of the meter type.

SERIALNO SET_ID METER_TYPE EXTERNAL_ID END_VALUE LOCATION
1234 123_abc meter1 123_abc_meter1 5 abc
1234 123_abc meter2 123_abc_meter2 10 abc
1234 123_abc meter3 123_abc_meter3 25 abc

 

I can do this be creating 89 sub jobs and querying each meter type and then populating that column, but just looking for another option.

 

Thanks for any help.

Talend Data Integration 

Labels (1)
1 Solution

Accepted Solutions
RVeitch_84
Creator
Creator
Author

I solved it by using a unpivot in my input sql.

 

View solution in original post

4 Replies
Pierrick
Partner - Contributor III
Partner - Contributor III

Hello @RVeitch_84,

if I understand correctly, you want to rotate your table to have each column in a row.
I suggest you take a look at the function Crosstable 

Vegar
MVP
MVP

Try something like this.Assuming you have 6 fields in the beginning of your table that you don't want to pivot.

 

new_table:

CROSSTABLE(meter_type, end_value,6) 

Load * from [table];

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think this is a Talend question.

RVeitch_84
Creator
Creator
Author

I solved it by using a unpivot in my input sql.