Shulou(Shulou.com)06/01 Report--

Pivot implements row and column transpositions in Oracle and the new version of Mysql, but some more complex transpositions are encountered when dealing with data, and pivot can't handle them, such as:

Want to convert to:

The difficulty lies in not knowing in advance how many sources of income there are, and that each person has different sources of income. First, we have to calculate the number of categories, dynamically generate the table structure according to the number, and then fill in multiple income data for each person in order.

The calculation process of SQL does not advocate step-by-step, and the set operation is not fully supported, so it is difficult to cope with this kind of multi-step complex calculation.

If you use the SPL language of the aggregator to deal with it, you can easily achieve:

AB1=connect ("db") = A1.query ("select * from Income") 2=B1.group (Name) = A2.max (~ .len ()) 3=create (Name,$ {B2. ("Source" + string (~) + ", Income" + string (~)). Concat@c ()})

4for A2=A4.Name | A4.conj ([Source,Income]) 5

> A3.record (B4)

The A3 grid gets the empty table of the desired data structure based on the original data; the B4 grid gets the data that everyone wants to fill in the empty table.

If you do not need complex calculation to get the result column, but only generate the column dynamically according to a certain field value, you can directly use the pivot function. The specific column does not need to be specified, but is automatically generated according to the field value:

= connect ("mysqlDB"). Query ("select * from t").

In addition to dynamic row and column transpose, there are complex transpose requirements such as two-way transpose and transpose between columns. These calculations are easy to be compiled with SPL, and refer to "transpose".

Aggregator SPL is a professional scripting language to solve SQL problems. It has simple syntax and accords with natural thinking. It is a natural step-by-step and clear process-oriented computing language. It uses a unified syntax independent of the database, and the algorithm can be seamlessly migrated between databases. It is a desktop-level computing tool, ready to use, simple configuration, perfect debugging functions, breakpoints can be set, single-step execution, and the results of each step can be viewed. See SQL problem solver

When the data is not in the database, it is still convenient for SPL to perform complex calculations:

= file ("d:/t.csv") .import (;, ",") .pivot...

SPL can be easily embedded in JAVA applications, please refer to "how Java invokes SPL scripts".

For specific usage, please refer to "how to use the aggregator".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.