esProc Simplifies SQL-style Computations – Transpose Rows and Columns.pdfVIP

esProc Simplifies SQL-style Computations – Transpose Rows and Columns During database application development, we often need to deal with complicated SQL-style computations. The transposition of rows and columns is one of them. Oracle uses pivot function to realize this computation. The other databases haven’t the counterparts to realize it directly, which makes the corresponding code difficult to write, understand and maintain. Besides, even the pivot function can only transpose the fixed columns, but is powerless about the unfixed ones. So are the other databases. Generally all of them must resort to the high level programming languages to realize the dynamic SQL. However, coding this computation with esProc will be concise and easy to understand. We ’ll use an an example to illustrate this. The following figure shows part of the SALES - a database table where order data are stored. OrderID Client SellerId Amount OrderDate 1 DSG 13 19480 2014-06-20 00:00 2 ERN 18 979 2014-06-13 00:00 3 JFE 19 28972 2014-12-11 00:00 4 OFS 21 4829 2014-02-24 00:00 5 ERN 22 21392 2014-02-01 00:00 It is required to compute the total order amount, the maximum and minimum order amount, and the total number of orders of each month of the year 2013, and then transpose the data into a table with thirteen columns and four rows, in which the four operations occupy the first column, with subtotal being the column name, and every month covers a column, with the column names being 1, 2, 3, 4 … The first five fields are as follows: subtotal 1 2 3