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 applicationsplease refer to How to Call an SPL Script in Java

For specific usage, please refer to  Getting started with esProc