Creates a crosstab query .
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
||An SQL aggregate function that operates on the selected data.
||A SELECT statement.
||The field or expression you want to use to create column headings in the query's result set.
||Fixed values used to create column headings.
When you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query .
TRANSFORM is optional but when included is the first statement in an SQL string . It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE , that specify additional selection or sorting criteria. You can also use subqueries as predicates — specifically, those in the WHERE clause — in a crosstab query.
The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.