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, GROUP BY clause, INNER JOIN operation, ORDER BY clause, SELECT statement, SQL subqueries, WHERE clause.

Example

This example uses the SQL TRANSFORM clause to create a crosstab query showing the number of orders taken by each employee for each calendar quarter of 1994. The SQLTRANSFORMOutput function is required for this procedure to run.

Sub TransformX1()

    Dim dbs As Database
    Dim strSQL As String
    Dim qdfTRANSFORM As QueryDef

    strSQL = "PARAMETERS prmYear SHORT; TRANSFORM " _
        & "Count(OrderID) " _
        & "SELECT FirstName & "" "" & LastName AS " _
        & "FullName FROM Employees INNER JOIN Orders " _
        & "ON Employees.EmployeeID = " _
        & "Orders.EmployeeID WHERE DatePart " _
        & "(""yyyy"", OrderDate) = [prmYear] "

    strSQL = strSQL & "GROUP BY FirstName & " _
        & """ "" & LastName " _
        & "ORDER BY FirstName & "" "" & LastName " _
        & "PIVOT DatePart(""q"", OrderDate)"

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    Set qdfTRANSFORM = dbs.CreateQueryDef _
        ("", strSQL)

    SQLTRANSFORMOutput qdfTRANSFORM, 1994

    dbs.Close

End Sub

This example uses the SQL TRANSFORM clause to create a slightly more complex crosstab query showing the total dollar amount of orders taken by each employee for each calendar quarter of 1994. The SQLTRANSFORMOutput function is required for this procedure to run.

Sub TransformX2()

    Dim dbs As Database
    Dim strSQL As String
    Dim qdfTRANSFORM As QueryDef

    strSQL = "PARAMETERS prmYear SHORT; TRANSFORM " _
        & "Sum(Subtotal) SELECT FirstName & "" """ _
        & "& LastName AS FullName " _
        & "FROM Employees INNER JOIN " _
        & "(Orders INNER JOIN [Order Subtotals] " _
        & "ON Orders.OrderID = " _
        & "[Order Subtotals].OrderID) " _
        & "ON Employees.EmployeeID = " _
        & "Orders.EmployeeID WHERE DatePart" _
        & "(""yyyy"", OrderDate) = [prmYear] "

    strSQL = strSQL & "GROUP BY FirstName & "" """ _
        & "& LastName " _
        & "ORDER BY FirstName & "" "" & LastName " _
        & "PIVOT DatePart(""q"",OrderDate)"        
        
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    Set qdfTRANSFORM = dbs.CreateQueryDef _
        ("", strSQL)

    SQLTRANSFORMOutput qdfTRANSFORM, 1994

    dbs.Close

End Sub

Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _
    intYear As Integer)

    Dim rstTRANSFORM As Recordset
    Dim fldLoop As Field
    Dim booFirst As Boolean

    qdfTemp.PARAMETERS!prmYear = intYear
    Set rstTRANSFORM = qdfTemp.OpenRecordset()

    Debug.Print qdfTemp.SQL
    Debug.Print
    Debug.Print , , "Quarter"

    With rstTRANSFORM
        booFirst = True
        For Each fldLoop In .Fields
            If booFirst = True Then
                Debug.Print fldLoop.Name
                Debug.Print , ;
                booFirst = False
            Else
                Debug.Print , fldLoop.Name;
            End If
        Next fldLoop
        Debug.Print
        
        Do While Not .EOF
            booFirst = True
            For Each fldLoop In .Fields
                If booFirst = True Then
                    Debug.Print fldLoop
                    Debug.Print , ;
                    booFirst = False
                Else
                    Debug.Print , fldLoop;
                End If
            Next fldLoop
            Debug.Print
            .MoveNext
        Loop
    End With
    
End Function

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following 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);

The next 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');