RaqForum 25 No.
296 View  • 
     ...

8.9 Dynamic row-to-column transposition related to primary table – sub table join

 
spl-cookbook(224)

This type of dynamic row-to-column transpositions is achieved through binding the sub table to the primary table dynamically.
Create the summary table recording the products bought by each customer in each day based on Order table and OrderDetail table.
The relationship between Order table OrderDetail table is that of the primary table and sub table. Each Order record corresponds to multiple OrderDetail records, as shown below:

imagepng

The number of OrderDetail records corresponding to each Order record is not always the same. Below is the desired result table:

ID Customer Date Product1 Amount1 Product2 Amount2 Product3 Amount3
1 3 20190101 Apple 5 Milk 3 Salt 1
2 5 20190102 Beef 2 Pork 4
3 2 20190102 Pizza 3

SPL script:

A
1 =connect(“db”) .query@x(“select * from OrderDetail left join Order on Order.ID=OrderDetail.OrderID”)
2 =A1.group(ID)
3 =A2.max(~.count()).(“Product”+string(~)+“,”+“Amount”+string(~)).concat@c()
4 =create(ID,Customer,Date,${A3})
5 >A2.run(A4.record([ID,Customer,Date]| ~.([Product,Amount]).conj()))

A1 Perform join query on Order table and OrderDetail table.
A2 Group joined records by order ID.
A3, A4 Generate column names dynamically according to the maximum number of members in the groups, and create a new table sequence.
A5 Loop through members of each group, concatenate result records together, and insert them into A4’s table sequence.

 

spl-cookbook(224)
 
 •  296 View  •