How to transpose a dynamic table
Key words: pivot dynamic transposition
In Oracle and the new version of MySQL, pivot implements row column transposition. However, when processing data, there will be some more complex transposition situations, and pivot can not fix them, such as:
| Name | Source | Income |
| David | Salary | 8000 |
| David | Bonus | 15000 |
| Daniel | Salary | 9000 |
| Andrew | Shares | 26000 |
| Andrew | Sales | 23000 |
| Robert | Bonus | 13000 |
Need to be transposed to：
| Category | Source1 | Income1 | Source2 | Income2 |
| David | Salary | 8000 | Bonus | 15000 |
| Daniel | Salary | 9000 | ||
| Andrew | Shares | 26000 | Sales | 23000 |
| Robert | Bonus | 13000 |
The difficulty is that we don't know how many kinds of income sources there are in advance, and each person has different kinds of income sources. First, we need to calculate the number of categories, dynamically generate table structure according to the number, and then fill in multiple income data of each person in order.
SQL does not advocate step-by-step calculation, and it does not fully support the set operation, so it is difficult to cope with this kind of multi-step complex calculation.
It would be easily implemented if esProc SPL could be used.
| A | B | |
| 1 | =connect("db") | |
| 2 | =A1.query("select * from Income").group(Name) | |
| 3 | =create(Name,${A2.max(~.len()).("Source"+string(~)+",Income"+string(~)).concat@c()}) | |
| 4 | for A2 | =A4.Name|A4.conj([Source,Income]) |
| 5 | >A3.record(B4) | |
According to the original data, A3 gets the empty table of the expected data structure; B4 gets the data that each person needs to fill in the empty table.
In addition to dynamic row and column transposition, there are complex transposition requirements such as bidirectional transposition and transposition with inter column operation. These calculations are easy to write with SPL. Refer to Transposition.
When the data is not in the database, it is still convenient for SPL to perform complex calculations:
=file(“d:/t.csv”).import(;,",")...
It's also easy to embed esProc into Java applications，please refer to How to Call an SPL Script in Java
For specific usage, please refer to Getting started with esProc
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
