Generate a Result Set with Dyamic Columns with esProc

Problem source：http://bbs.csdn.net/topics/391012377

Below is a selection from the original table (tb1):

Prjno      Subtask   Ddate      Num

P9996     P9996-sub002 2015-01-01     123

P9996     P9996-sub002 2015-01-02     134

P9996     P9996-sub002 2015-01-03     345

P9996     P9996-sub002 2015-01-04     55

T0071     T-007-01 2015-01-01     3333

T0071     T-007-01 2015-01-02     356

T0071     T-007-01 2015-01-03     178

According to a specified date, you need to get all projects before this date in the same month. Suppose the input date is 2015-01-03, you’ll get this:

Prjno     Subtask  2015-01-01     2015-01-02     2015-01-03

P9996     P9996-sub002 123  134  345

T0071     T-007-01 3333       356  178

esProc code for doing this:

A1: Query data from the beginning of the month to the specified date. d_date is an input date parameter, like 2015-01-03. pdate@m(d_date) calculates the first date of the current month.

A2: Create an empty result table sequence with dynamic columns according to the sequence of dates from the first date of the month to the specified date:

A3: The first part A1.group(Prjno,Subtask) groups A1’s data by Prjno and Subtask (esProc data grouping will keep the detail data of each group), then ~.groups(Ddate;sum(Num):Num), one by by, groups each group of data by the date and aggregate Num valules; finally, A2.record() writes each group name and the aggregate value into A2’s result table sequence. The following is the final result:

In a similar way any database is called, esProc can be called by the reporting tool or a JAVA program. The computed result in the form of ResultSet can be returned to JAVA main program via esProc JDBC. You can see related documents for detailed method.