TRANSFORM Statement (SQL Only)

See AlsosqlTransformSee                 ExamplesqlTransformExample>Low


You can use the TRANSFORM statement to create a crosstab query.  When you summarize data using a crosstab query, you select values from a specified field or expression1DUM8 as column headers so you can view data in a more compact format than a select query would produce.

TRANSFORM is optional but when included is usually the first statement in an SQL string29F05E5.  It precedes the SELECT statement.

A TRANSFORM statement has the following syntax:


          TRANSFORM  aggregatefunction
          PIVOT pivotcolumn


The argument aggregatefunction is an aggregate function that provides the data for pivotcolumn.

The argument sqlstatement includes 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.

The argument pivotcolumn refers to the field, column, or expression you want to pivot (rotate) to form the column headings.  When you pivot a field, the unique values of pivotcolumn become columns.  For example, pivoting the sales figures on the month of the sale in a crosstab query would create twelve columns.