Generate a Result Set with Dynamic 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:

esProc_sql_dynamically_column_1

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:

esProc_sql_dynamically_column_2

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:

esProc_sql_dynamically_column_3

 

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s