...

8.1 Row-to-column transposition

 
spl-cookbook(224)

A row-to-column transposition transforms multiple rows to a single row while transposing certain values of a specified field to new fields. Values of each new field come from another field of the corresponding row.
Find the highest score of each subject for each class based on StudentScore table.

Original table:

Class StudentID Subject Score
Class one 1 Math 89
Class one 1 Chinese 93
Class two 2 Math 92
Class two 2 Chinese 97

Transposed table:

Class MathMax ChineseMax
Class one 89 93
Class two 92 97

SPL script:

A
1 =connect(“oracle”)
2 =A1.query@x(“select Class, Subject, max(Score) MaxScore from StudentScore group by Class, Subject”)
3 =A2.pivot(Class; Subject, MaxScore; “Math”:“MathMax”, “Chinese”:“ChineseMax”)

A1 Connect to the database.
A2 Retrieve data from the database while getting the highest score of each subject in each class.
A3 Use pivot() function to transpose rows to columns.

spl-cookbook(224)
 
 •  188 View  •