Sometimes the source data needs to be transposed before displaying it in the report. But cross table can only handle the most basic transposition. Most of the scenarios need to be dealt with using SQL, report script or Java script. Each way has its own trick with high degree of difficulty.
Sometimes the source data needs to be transposed before displaying it in the report. But cross table can only handle the most basic transposition. Most of the scenarios need to be dealt with using SQL, report script or Java script. Each way has its own trick with high degree of difficulty.
esProc supports dynamic scripting, order-related calculations and set operations, and has the ability of realizing various types of data transpositions using one universal method, that is, retrieving data, creating empty result set and filling data in it. It is an ideal tool for report data source preparation. The reporting tool regards an esProc script as the stored procedure, passes parameter to it and gets the returned result after execution through JDBC. You can learn more from How to Use esProc to Assist Reporting Tools.
These are common transposition problems with database tables in report development and their solutions using the standard esProc method.
Transposing multiple columns to one row
Below is the format of the Students table:
name | age | sex | kg |
A | 10 | f | 30 |
B | 11 | f | 35 |
C | 12 | m | 33 |
You need to combine all records into one row in a format and display it in the report as shown below:
Aage | Asex | Akg | Bage | Bsex | Bkg | Cage | Csex | Ckg |
10 | f | 30 | 11 | f | 35 | 12 | m | 33 |
esProc script:
A2 creates an empty table sequence dynamically. A3 combines A1 into a single record and appends it to A2. ${} is used to parse a string into an expression for dynamic execution. string(field) means converting data of other types into the string type data. set.string() means concatenating members of a set into a string. The record function can be used to append records. You can also append records with this line of code: >A2.record(A1.conj(~.array().to(2,4)))
So you can see that the standard method of performing transposition includes 3 steps: Retrieving data in SQL, creating an empty result set using create function, and looping through the source data with run function and append new data to the result set with record function. The code for data appending could vary depending on different scenarios.
Multi-group transposition
In the database table kpi, every 4 records with the same f_site value is a group. You need to create a group report based on different f_site values. Each group has 5 columns of detailed data – they are “KPI Name”，“2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45”, and “2015-04-21 14:00”. The values of KPI Name are the three kpi columns – ioh_kpi, idh_kpi and iol_kpi. The values of the four date fields are the kpi details of each time period. Below is a selection from the source data:
dataset_date | f_site | ioh_kpi | idh_kpi | iol_kpi |
2015/04/21 13:15 | X6SF_SARF1 | 1 | 2 | 3 |
2015/04/21 13:30 | X6SF_SARF1 | 9 | 1 | 2 |
2015/04/21 13:45 | X6SF_SARF1 | 8 | 9 | 1 |
2015/04/21 14:00 | X6SF_SARF1 | 7 | 8 | 9 |
2015/04/21 13:15 | XC_01 | 2 | 3 | 4 |
2015/04/21 13:30 | XC_01 | 11 | 12 | 13 |
2015/04/21 13:45 | XC_01 | 21 | 22 | 23 |
2015/04/21 14:00 | XC_01 | 31 | 32 | 33 |
esProc script:
A2 gets the distinct dataset_date values, that is [“2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45″,”2015-04-21 14:00”]. B2 gets field names of A1 starting from the third field, i.e. [“ioh_kpi”,”idh_kpi”,”iol_kpi”]. A3 creates a two-dimensional table dynamically, with field names being site,KPI Name,”2015-04-21 13:15″,”2015-04-21 13:30″,”2015-04-21 13:45″,”2015-04-21 14:00″. A4-B5 use a loop statement to append data; the statement is equal to A1.group(f_site).run(…), but it is more clear than run function when steps are many. Here’s the result:
Then you just need to perform a group by site and then create a simple table for reporting.
Inverse transposition
A query finds that the tb1 table has one record with many fields, as shown below:
project | operator1 | actionTime1 | operator2 | actionTime2 | operator3 | actionTime3 | poerator4 | actionTime4 |
A | Ashley | 20140404 | Rachel | 20150101 | Emily | 20140909 | Ashley | 20150225 |
You need to create a report with three columns and multiple rows, as shown below:
project | operator | actionTime |
A | Ashley | 20140404 |
A | Rachel | 20150101 |
A | Emily | 20140909 |
A | Ashley | 20150225 |
esProc script:
((A1.fno()-1)/2) calculates the number of records that need to be added to the result set and run function runs a loop to append them.
If there is more than one record in the tb1 table and each record has a different project, you need to use the following code:
Here’s the result:
Inserting a subtable with specified maximum length dynamically into the main table
dColThread and dColQuestionare the main table and the subtable that are related through tID field. Each record of the main table corresponds to multiple but less than 5 status field values. You need to insert the status values between Phone and Decline fields and name them QuestionNo1、QuestionNo2…QuestionNo5.
Below is a selection of dColThread:
tID | ApplicationName | User | Phone | Decline |
A01 | mfc | Bill | +70000000 | 1 |
A02 | mfc | John | +18761221 | 2 |
A03 | java | Jack | +8014001231 | 6 |
A04 | mfc | Tim | +008613133123 | 4 |
A05 | db | John | +18761221 | 8 |
Below is a selection of dColQuestion:
qID | tID | status |
1 | A01 | yes |
2 | A01 | no |
3 | A01 | yes |
4 | A02 | yes |
5 | A03 | no |
6 | A04 | no |
7 | A04 | no |
8 | A05 | yes |
esProc script:
Create a two-dimensional table with fixed fields in A3; loop through each of A2’s groups to get the status value and complement the missing values to reach five; and then append the complete records to A3.
A3 gets the final result which can be displayed using the reporting tool’s table control directly:
Complementing the missing months before transposition
The tb table, with two fields – time and quantity, contains the product’s sales quantity per day. In certain months, the quantity value may be null. Below is a selection from the source data:
time | quantity |
2014-01-01 15:20:25 | 3 |
2014-02-21 16:11:23 | 2 |
2015-01-05 11:14:21 | 1 |
2015-02-11 15:21:11 | 2 |
You need to present 12 rows in the grid of the report, with fields including the fixed months (values are 1-12), and the annual sales quantity, as shown below:
Month | Quantity in 2013 | Quantity in 2014 | Quantity in … |
esProc script:
A1 performs a SQL group and aggregate; A2 gets a list of years; A3 groups A1 according to the sequence of 12 months; A4 dynamically creates an empty two-dimensional table; A5 loops through every group of A3 and appends one record each time. Below is the result of A3:
A4 gets the final result, as shown below:
Transposition with dynamic locating
In the database table tb, every 3 records with the same userid are a group. You need to transform these groups into rows and present result in a report with the table control. Below is a selection from the table tb:
userid | type | descr |
scooby | dog | dog |
scooby | weight | 50 |
scooby | hair | long |
mickey | mouse | mouse |
mickey | hair | |
mickey | weight | 2 |
The desired layout is:
userid | type | hair | weight |
mickey | mouse | | 2 |
scooby | dog | long | 50 |
esProc script:
The align function aligns data with members of a set ([‘hair’,’weight’]); @n means placing the unaligned data in a separate row. As with this example, such a row is composed of records corresponding to mouse and dog. Here’s the result:
There are three tables – Students table, Exam table and Retest table – which are related between each other through stu_id, as shown below:
Students
stu_id | stu_name | class_id |
1 | Ashley | 1-1 |
2 | Rachel | 1-1 |
3 | Emily | 1-3 |
Exam
stu_id | subject | score |
1 | java | 77 |
1 | c++ | 80 |
2 | java | 67 |
2 | c++ | 58 |
3 | java | 56 |
3 | c++ | 85 |
Retest
stu_id | subject | score |
2 | c++ | 78 |
3 | java | 82 |
You need to query the three tables to get the score of each subject, the total score and the retest score for every student. Below is the desired layout:
stu_id | stu_name | java_score | c++_score | scoresSum | javaRetest | c++Retest |
1 | Ashley | 77 | 80 | 156 | | |
2 | Rachel | 67 | 58 | 125 | | 78 |
3 | Emily | 56 | 85 | 141 | 82 | |
esProc script:
