All Statements
GROUP BY Clause
HAVING Clause
INNER JOIN Operation
LEFT JOIN Operation
PARAMETERS Declaration
RIGHT JOIN Operation
WITH OWNERACCESS OPTION Declaration
STATEMENT: TRANSFORM
PARAMETERS expression]
TRANSFORM aggregate function
SELECT anyselectstatement
PIVOT
pivotfield
[IN (
value1
[,
value2
[, ...] ] ) ]
The optional
TRANSFORM
statement applies an aggregate function to a
SELECT
statement and creates a crosstab query to display the results.
There are eight aggregate functions:
AVG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR, VARP
. They are used to calculate statistical information about a specific numeric field in a query.
A crosstab query is table that has both a row header and a column header. The data generated by using the aggregate function is listed in the table under a cross-reference between these row and column header. This is a convenient way to display data in a compact, summarized format.
The
PIVOT
portion of the statement is used to determine the column headers. You can use the returned values from
pivotfield
as headers. The term "pivoting" signifies that a result table generates headers based upon the selected values. For example, if you selected a specific period of years over which to generate data, then your column headers would have a column for each year. Or you can used the optional
IN
clause to create fixed headers by declaring
value1, value2, ...
The code example can be divided into four parts:
First, using an optional
PARAMETERS
declaration, the example displays a dialog box asking the user to choose the year for which to return figures.
Next the
TRANSFORM
statement applies the
SUM
aggregate function. Note that the
TRANSFORM
statement must always appear after any
PARAMETER
statement and before the
SELECT
statement.
Third, comes the mandatory
SELECT
statement.
Finally, the query returns for the selected year a list of different instruments with sales figures for each month (i.e., The
PIVOT
is pivoting over months, therefore each month, for which sales occurred, has an individual column):
PARAMETERS [Year?] Long;
TRANSFORM Sum([Shipping].Quantity * [Shipping].UnitPrice) AS GrossIncome
SELECT PurchaseOrder.InstrumentName FROM PurchaseOrder
INNER JOIN (InstrumentCataloq INNER JOIN [Shipping]
ON InstrumentCatalog.CatNum = [Shipping].CatNum)
ON PurchaseOrder.OrderNum = [Shipping].OrderNum
WHERE DatePart("yyyy", PurchaseDate) = [Year?]
GROUP BY PurchaseOrder.InstrumentName
ORDER BY PurchaseOrder.InstrumentName
PIVOT DatePart("m",PurchaseDate);
A possible result table for the above example:
JAN
MAR
MAY
JUN
AUG
SEP
OCT
NOV
DEC
drum
87.50
0.00
0.00
175.00
0.00
350.00
350.00
175.00
350.00
cornet
114.00
228.00
114.00
0.00
114.00
0.00
228.00
556.00
228.00
flute
179.00
179.00
0.00
0.00
179.00
0.00
358.00
716.00
358.00
trumpet
326.00
0.00
326.00
326.00
0.00
0.00
652.00
652.00
978.00
Note: For information about using "wildcard" characters, such as ?, with SQL, please read the Knowledge Base Article
A100219: SQL Wild Cards, ADO and Microsoft Access.
Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information