>
TRANSFORM Statement
Description
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.
See Also
FROM Clause, INNER JOIN Operation, ORDER BY Clause.
Example
This example creates a crosstab query that shows product sales by month for a user-specified year. The months are returned from left to right (pivoted) as columns, and the product names are returned from top to bottom as rows.
PARAMETERS [Sales for which year?] LONG;
TRANSFORM Sum([Order Details].Quantity * ([Order Details].UnitPrice -
([Order Details].Discount / 100) * [Order Details].UnitPrice))
AS Sales SELECT ProductName FROM Orders INNER JOIN(Products
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID)
ON Orders.OrderID = [Order Details].OrderID
WHERE DatePart("yyyy", OrderDate) = [Sales for which year?]
GROUP BY ProductName ORDER BY ProductName
PIVOT DatePart("m", OrderDate);
This example creates a crosstab query that returns product sales by quarter for each supplier for a user-specified year. The quarters are returned from left to right (pivoted) as columns, and the supplier names are returned from top to bottom as rows.
PARAMETERS [Sales for which year?] LONG;
TRANSFORM Sum([Order Details].Quantity * ([Order Details].UnitPrice -
([Order Details].Discount / 100) * [Order Details].UnitPrice)) AS Sales
SELECT CompanyName FROM Orders
INNER JOIN ((Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID)
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID)
ON Orders.OrderID = [Order Details].OrderID
WHERE DatePart("yyyy", OrderDate) = [Sales for which year?]
GROUP BY CompanyName ORDER BY CompanyName
PIVOT "Qtr " & DatePart("q", OrderDate) In ('Qtr 1', 'Qtr 2', 'Qtr 3',
'Qtr 4');