If we want to show the monthly sales, then we need to change the SQL statement to something like this:
TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO FROM ORDERS GROUP BY CUSTNO
ORDER BY CUSTNO PIVOT FormatDateTime('mmm yy', SaleDate)
And you will have the following result set:
In this cross-tab query, the column titles appear in the strange sequence shown in the previous Figure. The pivot statement automatically orders the columns alphabetically.
You could solve the column sequence problem by changing the PIVOT statement to PIVOT FormatDateTime('mm/yy', SaleDate) to provide column titles 01/95 through 12/96. However, many people prefer three-letter abbreviations for the months. Apollo SQL lets you use the IN() predicate to create fixed column names that appear in any sequence you specify. The following example demonstrates a PIVOT...IN statement that creates monthly column titles for any year:
TRANSFORM SUM(AMOUNTPAID) SELECT CUSTNO
FROM ORDERS
GROUP BY CUSTNO
ORDER BY CUSTNO PIVOT FormatDateTime('mmm', SaleDate)
IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
'Oct', 'Nov', 'Dec')
The preceding PIVOT statement gives the more satisfactory result shown in the following Figure:
The only restriction on the values of the IN() predicate's arguments is that, except for case, each value must exactly match the values returned by the formatted PIVOT statement. If you misspell an argument value, you will find that the data rows of the column with the mistaken header are empty.