denormalize rows in a table

One Star

denormalize rows in a table

I want to denormalize a table in MySQL Database.
Below is an example, table1 to be transformed to table2.
Any ideas please?

Table 1:
S.No NameSubject Marks
1 John English 60
2 John Hindi 59
3 John Telugu 70
4 John Math 75
5 John Science 69
6 John Social 55
7 Allen English 62
8 Allen Hindi 54
9 Allen Telugu 76
10 Allen Math 72
11 Allen Science 63
12 Allen Social 58
13 Peter English 49
14 Peter Hindi 52
15 Peter Telugu 60
16 Peter Math
17 Peter Science 79
18 Peter Social 53
Table 2:
S.No Name English Hindi Telugu Math Science Social
1 John 60 59 70 75 69 55
2 Allen 62 54 76 72 63 58
3 Peter 49 52 60 79 53
Thanks in Advance!
One Star

Re: denormalize rows in a table

To be able to perform this denormalization you must make sure that you know the names of the columns you want in your output.
If these column names can be variable you should not try the approach I'm about to explain. In that case you need some custom tJavaRow code.
If you do know the exact column names however, you can use a tMap in combination with a tAggregateRow as seen in my screenshots.
I've copied your data into a tab delimited file instead of a table and use a log row in stead of an output table, but the idea is the same.
Hope this helps!
Best regards,
One Star

Re: denormalize rows in a table

Thanks a lot. That worked for me..

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Downloads and Trials

Test drive Talend's enterprise products.