The Standard esProc Method of Assisting SQL-style Transposition

In many cases SQL is used to implement a transposition algorithm, such as static/dynamic transposition, natural/inverse transposition, single-/multi-group data transposition, transposition after data alignment/complementing and transposition with/without computed columns. There are different SQL implementations for different scenarios. Some need the pivot function, some require group+case when only, some use the union, and others depend on high-level languages to achieve the dynamic SQL. The lack of a standard method tends to cause confusion among programmers, who find it difficult to write the code.

As esProc (free edition is available) supports dynamic scripting, order-related calculations and set operations, it has the ability to realize data transposition in various scenarios using a standard method, that is, retrieving data, creating empty result set and filling data in it. Moreover, esProc can operate independently, as well as support console execution, invocation by reporting tools and Java applications. More details can be found in How esProc Assists Writing SQL Queries.

These are common transposition problems in SQL programming and their solutions using standard esProc method.

Simple transposition

The database table SALES stores order data, a selection of which is shown below:

You need to calculate the total order amount, the maximum/minimum order amount and the total number of orders each month in a specified year, and transpose the data into 13 columns and 4 rows. That is, the four algorithms constitute the first column named subtotal and each month is a column named 1, 2, 3, 4…

esProc script:

A1: Retrieve data and perform simple group and aggregate in SQL. Here’s the result:

A2: Create an empty result set containing fields “subtotal,1,2,3,4,5,6,7,8,9,10,11,12”. set.string() concatenates members of a set into a string delimited by commas; ${} parses a string as an expression and execute it dynamically.

A3: Fill in records to the empty result set one by one. The fno function gets the number of fields; the fname function gets field names by sequence numbers; the field function gets a column by the sequence number; the run function loops through a set\result set; and the record function appends records. Here’s the result of A2 after data appending:

According to this example, there are three steps to handle data transposition: retrieve data in SQL, create an empty result set using create function, and loop through the source data using run function and append records one by one using record function. The code for data appending will change a little in different scenarios. The following will explain each scenario in detail.

Dynamic transposition

The liquors table stores the production area, types and stock of liquors. Below is a selection from the source data:

You need to calculate the stock for every type of liquor from every production area, with each type corresponding to a field.

esProc script:

It can be handled still in steps of data retrieval, empty result set creation and data appending. A2 gets the names for the second to the N^th fields of the result set; the id function is equal to SQL distinct function. A4 groups A1’s data by Production. A5 loops through each group to append one record to A3 each time; the align function groups data according to a specified sequence and allows null members in a group.

A3 stores the final result, as shown below:

Transposing multiple columns to one row

Below is the format of the Students table:

You need to combine all records into one row in a format as shown below:

esProc script:

A2 creates an empty table sequence dynamically. A3 composes A1 into a single record and appends it to A2. string(field) means converting data of other types into the string type data. set.string() means concatenating members of a set into a string. You can also append records with this line of code: >A2.record(A1.conj(~.array().to(2,4))) .
Multi-group transposition
In the database table kpi, every 4 records with the same f_site value is a group. You need to transpose each group of data to transform dataset_date values to fields and ioh_kpi、idh_kpi、iol_kpi fields to values of KPI Name field. Below is a selection from the source data:

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:

Inverse transposition

A query finds that the tb1 table has one record with many fields, as shown below:

You need to transpose the table into a two-dimensional table with three columns and multiple rows, as shown below:

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, you need to use the following code:

Here’s the result:

Appending the subtable with irregular length into the main table

Table1 and Table2 are the main table and the subtable that are associated through ID field. Now you need to append the subtable as fields of the main table. The subtable has different numbers of rows with the same ID value to correspond one row in the main table.

Below is a selection from Table1:

Below is a selection from Table2:

The desired format:

esProc script:

Join the main table and the subtable using a SQL statement; group the data by ID; create an empty table sequence dynamically in A4 according to the group with the maximum number of records; and then concatenate the records in the main table and field values in the subtable into new records and append them to A4. Unlike a SQL group operation, esProc group function is able to group data without aggregating it.

A4 gets the final result as follows:

Inserting the subtable with specified maximum length dynamically into the main table

dColThread and dColQuestion are 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:

Below is a selection of dColQuestion:

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 as follows:

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:

You need to rearrange the tb table into 12 rows, with fields including the fixed months (values are 1-12), the unfixed annual sales quantity (if a time period involves 2 or 3 consecutive years, create 2 or 3 fields), as shown below:

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 computed columns

Below is a selection from the table tb1:

You need to generate the total number of projects before a specified date in the current month. For instance, enter 2015-01-03 and here’s the desired result:

esProc script:

Query the source data and group it; loop through every group to insert one record to the empty two-dimensional table each time.
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. Below is a selection from the table tb:

The desired result:

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:

Column-to-row transposition with three related tables

There are three tables – Students table, Exam table and Retest table – which are related between each other through stu_id, as shown below:

Students

Exam

Retest

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 result:

esProc script: