Pivot- Transpose columns to row

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

Thanks - its working as expected.