Generate a Result Set with Dynamic Columns with esProc

Problem source: .

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,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.


About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in SQL-related Puzzle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s