Sometimes, the user may want to extract some information from the existing table into a new table in a way that the values of the attribute in the original table becomes the attributes of the new table. We call this type of table transformation "trans-pivot". The user can use the Pivot Table Conversion method for trans-pivot requests.

To achieve the desired result, the user generally performs two steps. The first step is to aggregate the pivot attribute and pivot attribute values in the source table, so each raw of data will be unique.
The general syntax for Aggregated Source formation statement is following:

BUILD TABLE aggregate_source_table AS (Find group attribute, pivot attribute, aggregate function(pivot_value_attribute) FROM source table)

The second step is to perform Trans-pivot from the aggregate table. The general syntax for Trans-pivot statement is following:

BUILD TABLE pivot_table_name (group attribute, pivot attribute[select key,...]: pivot_value_attribute) FROM aggregate_source_table

Let's see how this works in the context of our ABC company case. In our earlier example, ABC company staff has built a sales table that looks like following:

The staff of ABC company is interested to know the effect of channel for each product offering. He may do this by collecting the sales quantity result of each channels for each products from the sales data. To do this, he need to first aggregate the information from the sales table.

BUILD TABLE aggr_source AS (Find Product.id, channel_name, SUM(qty) from sales)

The resulting aggr_source table may looks like this:

Then, he can transform above data into another pivot table.

BUILD TABLE pivot_table1 (Product.id , channel_name[*]:'SUM(qty)') FROM aggr_source

pivot_ table1 is created and looks like below:

If the staff is only interested to find out the result about convenient stores type of channel, then he can further specify the stores in the pivot table.

BUILD TABLE pivot_table2 (Product.id , channel_name['7-11', 'am/pm', 'CVS' STRING ]:'SUM(qty)') FROM aggr_source

The pivot_table2 will be created as following:

There are also applications where aggregating the source prior to trans_pivot is not necessary. For example, instead of finding a particular result (i.e. performance of channels on each product), the staff may be just want to re-arrange its existing sales table in a way that is easier for others to read. For instance, he may want to expand the value of channel attribute in an effort to show all the available channels. For this case, he does not need to aggregate the sales table but just simply trans-pivot the sales table with group attribute set as '*'.

BUILD TABLE pivot_table3(* , channel_name[*]:qty) FROM sales

The resulting pivot_table3 will be arranged as following:

Another useful minor trick in utilizing pivot table is to check whether the pivot values exist or not.
Let's assume the staff of ABC company just wants to find out that for each product which channel did not carry or sales transaction did not occur. He can quickly find out by just trans_pivot the existing sales table without setting pivot values attribute.

BUILD TABLE pivot_table4 (Product.id , channel_name[*]) FROM sales

The resulting pivot_table4 will be looked like following:

The result attribute values in pivot_table4 will be set to either one or zero to represent whether the attribute exists or not. Thus, the ABC company staff can easily find what he is looking for with those attribute values equal '0'.

Auxiliary Clauses

Default_type:

Default type is used to assign the default data type of all pivot values. If the command does not specify the default_type, the default setting will be VARSTRING.
Example of default_type setting using earlier command in this chapter:

BUILD TABLE pivot_table1 (Product.id , channel_name[*]:'SUM(qty)') FROM aggr_source {default_type: DOUBLE}

Filter:

Filter condition can also be applied in the auxiliary clauses. In our previous pivot_table2 example, we can filter out to show only those with total quantity greater than 10 with following command:

BUILD TABLE pivot_table2_filter (Product.id , channel_name['7-11', 'am/pm', 'CVS' STRING ]:'SUM(qty)') FROM aggr_source {filter: SUM(qty)>10 & default_type:INT32}

The resulting filter table will be generated as below:

append:

When a table existed and the append auxiliary is set to "true", all results will be appended to the table.

BUILD TABLE pivot_table3(* , channel_name[*]:qty) FROM sales{append:true}
BUILD TABLE pivot_table2 (Product.id , channel_name['7-11', 'am/pm', 'CVS' STRING ]:'SUM(qty)') FROM aggr_source {append:true}

Note: please ensure the appending data schema is the same to the existed table.

srcOffset:

Set the offset number of the source table ex. append the pivot table (row 9951 to the end of the table)

BUILD TABLE pivot_table5(order_id , channel_name[*]:qty) FROM sales{srcOffset:9950}

srcEnd:

Set the ending row of the source table ex. row 9951~9970

BUILD TABLE pivot_table5(order_id , channel_name[*]:qty) FROM sales{srcOffset:9950 & srcEnd:9970}