Apollo SQL's cross-tab query feature solves the data cell rearrangement problem. Two Apollo SQL keywords, TRANSFORM and PIVOT, handle the denormalization of the data. The elements of an Apollo SQL cross-tab query are as follows:
•The object of the TRANSFORM predicate specifies the values for the data cells and must contain one of the SQL aggregate functions, such as SUM() or AVG().
•The SELECT statement designates the row titles. The field you specify with the SELECT statement must be the same as the GROUP BY field.
•The FROM statement specifies each table involved in the query. The tables may be identified by the INNER JOIN statements, without the conventional comma-separated table_names list if you want to do joinings.
•The GROUP BY clause aggregates row data. Only one field is permitted in the GROUP BY clause of a conventional cross-tab query.
•The optional ORDER BY clause sorts the rows by the value of the single field specified in the SELECT and GROUP BY statements.
•The PIVOT statement determines the column grouping and supplies the column titles. Column titles consist of the value of the grouping criterion. The object of the PIVOT predicate takes the place of the second GROUP BY field of the SQL statements of the two examples in the preceding section.
The Apollo SQL syntax for performing the cross-tab query is this:
TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO FROM ORDERS GROUP BY CUSTNO
ORDER BY CUSTNO PIVOT EXTRACT(YEAR FROM SALEDATE)
This SQL statement will give us this cross-tab query as a result set:
As you can see, the first column shows the customer ID, and the rest of the columns shows the sum of sales for that customer for every year involved.
Apollo SQL will create columns for every distinct year found on the field SALEDATE for all the orders in the database. This is known as dynamic columns.