8.9 Dynamic row-to-column transposition related to primary table – sub table join
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:
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 Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
