Key words:   pivot   without aggregation

The latest version of the mainstream database provides pivot function, which is more convenient to handle some static row and column transpose queries. However, because it is bundled with aggregation operation, it will cause some understanding problems. See the following example:

Need to be transposed to：

This is a simple transpose operation without aggregation, but when using pivot in SQL, you have to aggregate it with Max / min. Although the action is not big, it does cause difficulties for novices to understand. If you don’t have a deep understanding of pivot and unpivot, you often misunderstand them as reciprocal operations. In fact, it is not because the aggregation of binding is irreversible (the detailed data can sum, but the sum cannot calculate the detailed data).

If the SPL language which advocates step-by-step calculation is used, there will be no such misunderstanding. The pivot in SPL is designed as a pure transpose action, which is simple to write and understand:

=connect(”mysqlDB”).query(“select * from t”).pivot(CustomerID; DBColumnName, Data)

 In addition to the static transposition with clear data structure, there are complex transposition requirements such as dynamic transposition with unknown data structure, 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