JetSQL » Statements » TRANSFORM
- Syntax:
- 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.
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, ...
Examples
Code:
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); Output:
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
Explanation:
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)
Language(s): MS SQL Server
See Also:
