One Star

denormalize rows in a table

Hi,
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!
2 REPLIES
One Star

Re: denormalize rows in a table

Hi,
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,
Arno
One Star

Re: denormalize rows in a table

Thanks a lot. That worked for me..