Looping through multiple columns Tmap

[RVeitch_84]

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 

[Pierrick]

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]

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]

I think this is a Talend question.

[RVeitch_84 · Accepted solution]

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