topic Pivot- Transpose columns to row in Talend Studio

Pivot- Transpose columns to row

Anonymous — Fri, 08 Jun 2018 16:47:35 GMT

Hi Community,

i am trying to converts rows to columns and below is my scenario, source and destination are MSSql tables

Input :

Date Student Subject Marks

01/01 A Maths 20

01/01 A Sci 19

01/01 A Lang 18

01/01 B Maths 15

01/01 B Lang 19

Expected output :

Date Student Maths_Marks Sci_Marks Lang_Marks

01/01 A 20 19 18

01/01 B 15 NULL 19

I tried with tdenormalise and tpivotToColumnDelimited but not working as expected. Please let me know if anyone has resolved this on.

Thanks, Prashant

Re: Pivot- Transpose columns to row

Anonymous — Mon, 11 Jun 2018 15:28:57 GMT

The way I would do this is as follows....

1) Connect your source to a tMap. The input will be automatically created, for the output create the structure you want in your output.

2) Since you know your keys (Maths, Sci, Lang), put rules in the tMap variables to assign values for the output fields (Maths_Marks, Sci_Marks, Lang_Marks). For example, create a variable called "Maths_Marks" and add an expression like like this....

row1.Subject.compareToIgnoreCase("Maths")==0 ? row1.Marks : null

I've assumed your column and row names above. This will assign the Marks value to this column when the Subject is "Maths".

3) Do the above for all your subject columns and connect the tMap variables to their respective output column. This will keep the same number of rows, but will split the values across the corrcte columns.

4) Add a tAggregateRow component. Group by Date and Student. For each of the subject columns use the "Max" operation.

This will return the data in the way you require.

Re: Pivot- Transpose columns to row

Anonymous — Mon, 11 Jun 2018 21:20:06 GMT

Thanks - its working as expected.