RaqForum 25 No.
266 View  • 
     ...

8.10 Dynamic row-to-column transposition related to multi-table join

 
spl-cookbook(224)

Perform dynamic row-to-column transposition, during which column names are dynamically generated according to the original field values and enter result data of join query into new columns.
Generate a summary table recording each student’s scores of all subjects, total score and make-up test scores based on Students table, Retest table and Exam table.

imagepng

The summary table’s structure is as follows:

stu_id stu_name Chinese_score Math_score total_score Chinese_retest Math_retest
1 Ashley 80 77 157
2 Rachel 58 67 125 78
3 Emily 85 56 141 82

SPL script:

A B
1 $()select t1.stu_id stu_id,t1.stu_name stu_name,t2.subject subject,t2.score score1,t3.score score2 from Students.txt t1 left join Exam.txt t2 on t1.stu_id=t2.stu_id left join Retest.txt t3 on t1.stu_id=t3.stu_id and t2.subject=t3.subject order by t1.stu_id,t2.subject
2 =A1.group(stu_id) =A1.group(subject)
3 =create(stu_id,stu_name,${(B2.(~.subject+“_score”)|“total_score”|B2.(~.subject+"_retest")).concat@c()})
4 >A2.run(A3.record([stu_id,stu_name]|B2.(~(A2.#).score1)|A2.~.sum(score1)|B2.(~(A2.#).score2)))

A1 Join Students table, Exam table and Retest table and retrieve data, and sort data by stu_id and subject. In the code, score1 represents the regular test score and score2 is the make-up test score.
A2, B2 Group the joined table by stu_id and subject respectively.
A3 Generate column names dynamically according to subjects and create the result table sequence.
A4 Loop through the grouped student score records in A2, calculate and generate new values and concatenate them together, and insert result records into A3’s table sequence.

spl-cookbook(224)
 
 •  266 View  •