Creates a crosstab query.
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
| Part | Description |
| aggfunction | An SQL aggregate function that operates on the selected data. |
| selectstatement | A SELECT statement. |
| pivotfield | The field or expression you want to use to create column headings in the query's result set. |
| value1, value2 | Fixed values used to create column headings. |
Remarks
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.