【Question】

Here’s my table:
Name  Data  Time
A     2     20140101
B     3     20140107
C     4     20140104
A     3     20140109
A     1     20140206
C     3     20140301
C     2     20140201
A     1     20140207
Perform sum by Name and Time period. Here’s the final form I want. Is there any way to do this?
Name  201401  201402  TimePeriod3………………
A     5       2       ……    ………………
B     3       0       ……    ………………
C     4      2 3 ……   ………………

【Answer】

If you want to build a report, this is a typical crosstab sheet. You can use any reporting tool that supports horizontal expansion to do this. Here’s how to do it in RaqReport:

Report data set in SQL: select Name,left(Time,6) as Time,Data from SHIP_BERTH

Here are the expressions in the reporting tool:

If the computing goal is to provide a data source for an application, it’s hard to generate a result set with dynamic columns in dynamic SQL. But this is easy in SPL:

A1: Retrieve data from the database;

A2: Group data by Name and Time and perform sum over each group;

A3: Transpose values in Time column and Data column in A2 into new rows;

This is the final table sequence:

You can call the script from a Java application via esProc JDBC. See