|
<< Click to Display Table of Contents >> Navigation: Apollo SQL > DDL & DML Statements > TRANSFORM. . . PIVOT |
The cross-tab query is a special form of SELECT query that summarizes data in spreadsheet style, most often in time-series format. You are likely to find that 75 percent or more of the decision-support applications you create for your firm or clients include cross-tab queries. In many cases, the cross-tab query result set serves as the data source for graphs and charts.
Example 1:
TRANSFORM SUM(AMOUNTPAID)
SELECT CUSTNO FROM ORDERS
GROUP BY CUSTNO
ORDER BY CUSTNO
PIVOT EXTRACT(YEAR FROM SALEDATE)
Example 2:
TRANSFORM CAST(SUM(AmountPaid) AS MONEY)
SELECT CUSTNO FROM ORDERS
GROUP BY CUSTNO
PIVOT FormatDateTime("mmm", SALEDATE)
IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
Example 3 (Sub-Queries)
PIVOT also support sub-queries, such as:
TRANSFORM SUM(AmountPaid), COUNT(*) AS TotPaid, AVG(AMOUNTPAID) AS AvgAmt
SELECT CUSTNO
FROM ORDERS
GROUP BY CUSTNO
PIVOT FormatDateTime("yyyy", SALEDATE)
IN (SELECT DISTINCT EXTRACT(YEAR FROM LASTINVOICEDATE)
FROM CUSTOMER ORDER BY 1)
MORE:
•Using Cross-tab Queries to Present Summary Data
•Summary Data Created by the GROUP BY Clause