Transpose column to row

[Anonymous]

I have an input like

id,name,sales1,sales2,sales3
123,Tim,abc,pqr,xyz

I need my  output as

id,name,sales

123,Tim,abc

123,Tim,pqr

123,Tim,xyz

Please help me.

[TRF]

Use tSplitRow with the following settings and schema:

[manodwhb]

@Matthew ,you can use tUnpivotRow component ,which is open source component. need to download from talend exchange.

You can refer to this documentation to learn how to install a custom component to talend studio.
https://help.talend.com/reader/2AWmA~w4VvlfP3JC7dTR2w/Kvx1JE1dQGJgGhfFoHK8xA

[Anonymous]

Thank you for the replies. How will be the performance? Which will be better tsplitrow or tunpivot row?

[TRF]

tSplitRow is lighter

[Anonymous]

Just modifying my input

id,col1,col2,col3

101,a,b,a

102,a,a,a

103,b,a,b

My output should be like

101,1,a

101,2,b

101,3,a

102,1,a

102,2,a

102,3,a

103,1,b

103,2,a

103,3,b

I have done half part using tnermalize and got the output as

101,a

101,b

101,a

102,a

102,a

102,a

103,b

103,a

103,b

[TRF]

Still the same:

[Anonymous]

tsplitrow will not work, since I need to get the count as incremented value

[ankit7359]

Hi @Matthew ,

You can use tmap and add in a field for holding incremental sequence. Also you have to add a if-else statement -> like if the id is 101 then call numeric.sequence else check for 102 then call numeric.sequence etc.. until the number of id's u have... Also in the last else you can give it as 0 as the datatype of the field is numeric/integer.

Thanks,

Ankit

[TRF]

@Matthew tSplitRow works as you have just 3 columns you want to dispatch over 3 rows with an indice varying from 1 to 3.

Here is the result:

.---+------+---.
|  tLogRow_18  |
|=--+------+--=|
|id |indice|col|
|=--+------+--=|
|101|1     |a  |
|101|2     |b  |
|101|3     |a  |
|102|1     |a  |
|102|2     |a  |
|102|3     |a  |
|103|1     |b  |
|103|2     |a  |
|103|3     |b  |
'---+------+---'