...

8.2 Column-to-row transposition

 
spl-cookbook(224)

A column-to-row transposition generates a new row for each to-be-transposed field, makes field names or related names of fields the values of corresponding new field, and assigns values of original fields to another new field.
Generate a table of scores for all subjects according to StudentScore table.

Original table:

StudentID Math Chinese
1 89 93
2 92 97

Transposed table:

StudentID Subject Score
1 Math 89
1 Chinese 93
2 Math 92
2 Chinese 97

SPL script:

A
1 =connect(“oracle”)
2 =A1.query@x(“select * from StudentScore”)
3 =A2.pivot@r(StudentID; Subject, Score; Math:“Math”, Chinese:“Chinese”)

A1 Connect to the database.
A2 Retrieve data of StudentScore table.
A3 The pivot() function works with @r option to transpose columns to rows.

 

spl-cookbook(224)
 
 •  134 View  •