Simplified SQL for dynamic row and column transpose

The calculation of dynamic row and column conversion is often seen in real business, and 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 is generally done in the following ways:

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. You need to specify the target column when you use it, and the scene for the dynamic column cannot be completed directly.

2. Use case expressions

for databases that do not support pivot. such as MySQL, DB2. Ability to use case when conditional expression is complete. Similar to pivot, it is necessary to write dead according to the target column, unable to write the dynamic column structure transformation directly.

for the case of dynamic columns. 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 the differences between the database, the writing and the degree of difficulty are not the same. Unable to write a generic SQL statement.

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

The purpose of row and column conversions is often for further data rendering. This means that there will be a main program (such as a reporting tool, etc.) that accepts the results for next steps. If it is a Java main program, you can use the run-dry collector (free version) to assist with such conversions. The collector is a script that dynamically interprets the run. The code of the row and column conversion is more versatile.

The collector provides a JDBC interface that can be placed between Java applications and databases. Let the application continue to run the collector script as if it were a database, without changing the application structure.

Here is a simple example of how to implement a row and column transformation with a collector. and integrated into the Java main program.

1, simple row to column

A general row-to-column simply turns a row of data into a result column. Complex inter-column calculations are not involved.

Convert the following Student scores table into a collection of sub-account presentations:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Target Result:

Implementation script:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

A1: Run 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. Each row of data is then appended to the result set (A5,B5). In the case of the step calculation mechanism of supporting data table object, the process of row-to-column can be written according to natural thought.

The calculation results of the collection script can be returned to the Java main Program or the report tool with 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 (like a stored procedure), P1 is the name of the collector script file

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

                        // Run the 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 scheduler script and visiting the database is exactly the same, the ape that is familiar with JDBC can be mastered very quickly.

More specific information on the deployment and invocation of the collector JDBC can be seen in the Java invocation of the "Integrated application of the collector."

2. Row to column for indefinite long grouping in the example above, the column of the result set (that is, the account) is often known beforehand, so it is not very difficult to write it out in static pivot (or case) syntax. But assuming that the columns of the result set need to be calculated dynamically, it is very 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 result columns after conversion based on the maximum unit group length. Target Result:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Implementation script:

A1: Run SQL to fetch numbers from the production table;

A2: Group by unit. Grouping results in the collector retain grouped results (members) for easy 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 a cyclic A2. The categories and yields in each grouping are written to the A5 result sequence table.

similar to the above. This code is still generating an empty result set on a dynamic basis. And then calculate the appropriate data to append.

The calculation of this example requires the writing of dynamic SQL to spell out the result set, but the number of columns is known because the largest group is to be found. The result is not the same as the normal pivot to be able to use the field values directly corresponding columns. This is to write the stored procedure is completed step by step is convenient.

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

3, including row-to-column calculation between columns

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

required to output the monthly payable amount according to the specified year (e.g. 2014), if there is no data for the month, the amount payable in the month is the value of the month.

Target Result:

Implementation script:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

A1: Run 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 set the payable amount for the corresponding month. B6 the null value to the value of the previous one months. B7 the record into the result order table.

The process of operation is still to append data after generating an empty result set, different. The data to be appended here need to be often a series of computational abilities.

The set-up script supports sequential operations. So very easy to take the value of the previous record. Computed between columns for dynamic row and column conversions. Compared to complex SQL or stored procedures. The set-up script is clearer and easier to understand.

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). For example, the following data, the number of columns variable:

Target Result:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Implementation script:

A1: Run SQL Fetch number;

A2: Create an empty sequence table of target results;

A3: Calculates the number of rows to split for each record according to 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.

Simplified SQL for dynamic row and column transpose