【Question】

There are 3 tables: Student table, OriginalScore table and RetakeScore table:

Student table

stu_id     stu_name    class_id

1         Tom              1-1

2         Lee               1-1

3         Bob             1-2

 

OriginalScore table

stu_id     subject    score

1            java        77

1            c++        80

2           java        67

2           c++        58

3           java        56

3           c++        85

 

RetakeScore table

stu_id     subject    score

2            c++         78

3           java         82

 

Here’s the table I want after querying the three tables:

stu_id  stu_name  java_score  c++_score  Total score  java_RetakeScore  c++_RetakeScore

1         Tom         77              80               156

2         Lee         67              58               125                                             78

3         Bob         56             85               141              82

Is there anyone who can tell me how to do this in SQL? Thanks.

 

【Answer】

This is dynamic row-to-column transposition. To do it in SQL, you need to dynamically generate a query. That’s a hassle. But it’s easy to handle this in SPL:

	A		B
1	$select   t1.stu_id stu_id,t1.stu_name    stu_name,t2.subject subject,t2.score score1,t3.score score2 from Student.txt   t1 left join OriginalScore.txt t2 on t1.stu_id=t2.stu_id left join   RetakeScore.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)
3	=A1.group(subject)
4	=create(stu_id,stu_name,${(A3.(~.subject+"_score")|"scores(Total   score)"|A3.(~.subject+"RetakeScore")).concat@c()})
5	for A2	>A4.record([A5.stu_id,A5.stu_name]|A3.(~(#A5).score1)|A5.sum(score1)|A3.(~(#A5).score2))

A1: 3-table association and data retrieval. A left join is required to make sure that the Student table is wholly retained;

A2: Group A1’s data by stu_id;

A3: Group A1’s data by subject;

A4: Create a new table sequence to hold the final result set;

A5-B5: Loop through each stu_id group to dynamically add the resulting records according to the subject group. Here’s A4’s final result: