Different databases have different support for row column transposition. MySQL does not provide it, and it generates columns one by one through case when; Oracle provides pivot and unpivot functions, which is more convenient, but it also needs to specify the column names in advance. If you don't know how many columns there are in advance, it's difficult to use SQL alone. For example, you can dynamically spell SQL through applications like Java.

This complex row column transposition, if written in SPL language, could be realized in one line, transposing into fixed columns n1, n2...:

=connect(”mysqlDB”).query(“select * from t”).pivot(g1;g2,f;v1:n1,v2:n2…)

According to the value of the f field, the column is generated dynamically, which is still the same sentence, just removing the parameters of the result column:

=connect(”mysqlDB”).query(“select * from t”).pivot(g1;g2,f)

In addition to dynamically generating columns, there are also complex transpose requirements such as bidirectional transpose, inter column calculations while transposing, and column names cannot be determined. These calculations are easy to write with SPL. Please 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(;,",").pivot...

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