Simplified SQL for dynamic row and column transpose

The calculation of dynamic row and column conversion is very common in the actual business, there are discussions on various technical forums on the Internet, such as the following questions:

http://www.iteye.com/problems/87788

http://bbs.csdn.net/topics/390869577

http://bbs.csdn.net/topics/391000711

http://bbs.csdn.net/topics/391001035

http://bbs.csdn.net/topics/390888703

http://bbs.csdn.net/topics/391012377

http://bbs.csdn.net/topics/390956910

http://bbs.csdn.net/topics/391004719

http://bbs.csdn.net/topics/390946260

http://bbs.csdn.net/topics/390937222?page=1#post-398564938

http://bbs.csdn.net/topics/390883416

http://bbs.csdn.net/topics/390960953

http://bbs.csdn.net/topics/390959646

Row-to-column use of SQL completion generally has the following methods:

1. Use row and column conversion functions

oracle11g and above and mssql2005+ provide row-and-column transpose operators pivot and UNPIVOT, which are used for rows and columns, which are used for column changing, when used to specify target columns, and for dynamic column scenarios that cannot be done directly.

2. Use case expressions

for databases that do not support pivot, such as MySQL, DB2, you can use the case when conditional expression to complete. Similar to pivot, you need to fix the write death based on the target column and cannot write the dynamic column structure transformation directly.

in the case of dynamic columns, you can only:

3. Splicing Dynamic SQL

When dealing with dynamic row and column conversion, it is often necessary to splice dynamic SQL in the stored procedure, because of the differences between the database, the writing and the difficulty degree are different, and cannot write the common SQL statement.

in the actual situation, the row and column conversion is often accompanied by the calculation, increasing the difficulty of the transpose.

The goal of row and column conversions is often for further data rendering, meaning that a main program, such as a reporting tool, accepts the results for next steps. In the case of a Java main program, you can use the run-dry collector (free version) to assist with this type of conversion. The collector is a script that dynamically interprets execution, and the code that completes the row-and-column conversion is more versatile. The collector provides a JDBC interface that can be placed between the Java application and the database, allowing the application to continue to execute the collector script as if it were accessing the database without altering the application structure.

The following is a simple example of how the collector can be used to implement row and column conversions and integrate into the Java main program.

1, simple row to column

The normal row-to-column columns simply convert the data rows to the result columns, without involving complex inter-column calculations. Convert the following Student scores table into a collection of sub-account presentations:

Target Result:

Implementation script:

A1: Execute SQL Fetch, and sort by ID, subject;

a2-a3: Grouped by ID and subject, the collector retains a subset of packets for later calculation;

A4: Dynamically creates an empty target result set;

A5-B5: The student group of the cyclic A2, according to the subject grouping, writes the student ID, the name and each subject result to the result set;

A6: Returns the result set.

From the above code, we can see the basic steps of using the collector to implement row-to-column: the empty target result set (A4) is computed dynamically, and then each row of data is appended to the result set (A5,B5). In the case of a step calculation mechanism that supports the data table object, the process of row-to-column can be written according to natural thinking.

The calculation results of the collection script can be returned to the Java main Program or the report tool using the JDBC interface, and the Java call set is the script code:

class.forname ("Com.esproc.jdbc.InternalDriver");

Con=drivermanager.getconnection ("jdbc:esproc:local://");

                    // Call the Collector script (similar to a stored procedure) where P1 is the collector script file name

St= (com. esproc.jdbc.InternalCStatement) con.preparecall ("Call P1 ()");

                        // Execute Script

St.execute ();

                    // Get result set

Resultsetrs = St.getresultset ();

......

The return value is the ResultSet object that conforms to the JDBC standard, and the method of invoking the collector script is exactly the same as accessing the database, and programmers familiar with JDBC can quickly master it.

More detailed information on the deployment and invocation of the collector JDBC can be found in the "Java call" of the Integrator Integration application.

2. Row to column for indefinite long grouping in the previous example, the column of the result set (that is, the account) is often known beforehand, so it is not difficult to write it out in static pivot (or case) syntax. But if the columns of the result set need to be calculated dynamically, it is difficult to use pivot. The number of product columns produced by each type of mechanism in this example is variable:

It is required to determine the number of converted result columns based on the maximum unit group length, the target result:

Implementation script:

A1: Execution SQL takes a number from the production table;

A2: Grouped by unit, the result of grouping in the collector retains the grouped result (member) to facilitate subsequent use and calculation;

A3: The maximum number of members in a group to determine the number of result set columns;

a4-a5: Create an empty result set dynamically;

a6-b7: A grouping result in the loop A2 that writes the categories and yields in each group to the A5 result sequence table.

Similar to the above, the code still generates an empty result set dynamically and then calculates the appropriate data append.

The calculation of this example needs to write the dynamic SQL to spell out the result set, but because to find out the largest group to know the number of columns, the result is not like the general pivot can be used to directly correspond to the field value column, it is necessary to write the stored procedure step-by-step to complete the convenience.

Relatively complex stored procedures, the set-up script supports procedural calculations, the code is more concise, easy to write.

3. Row-to-column calculations with columns

As mentioned at the beginning, the row and column conversions are often accompanied by columns, such as data:

required to output monthly payables based on a specified year (e.g. 2014), if no data for the current month, the amount payable in the month is the value of the month.

Target Result:

Implementation script:

A1: Execute SQL Fetch query year data;

A2: Generates an empty sequence table with 12 months of results;

A3: Group by Customer;

a4-b7: Loop grouping, B5 sets the payables amount for the corresponding month, B6 the null value to the value of the first one months, B7 the record into the result order table.

The operation is still an empty result set after the append data, the difference is that the data to be appended here need to be a series of calculations to get.

The set-up script supports sequential operations, so it is easy to take the value of the previous record. For inter-column calculations that occur during dynamic row-and-line conversions, the set-up scripts are more readable than complex SQL or stored procedures.

4. List of career change In addition to the transpose mentioned above, there are times when a row of multiple columns of data is converted into multiple rows of data (a career change). The following data, where the number of columns is variable:

Target Result:

Implementation script:

A1: Execute SQL Fetch;

A2: Create an empty sequence table of target results;

A3: Calculates the number of rows to split for each record based on the number of columns in the A1 collection;

a4-b4: Loop A1 collection, dynamically fetching each column of data into the A2 result sequence table.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Simplified SQL for dynamic row and column transpose