Transform

Create and run a crosstab query.

Syntax
      TRANSFORM aggFunction SelectStatement PIVOT
         pivotField [IN (value1[, value2[, …]])]

Key
   aggFunction     An SQL aggregate function that operates on the selected data.

   SelectStatement A SELECT statement.

   pivotField      The field or expression used to create column headings
                   in the query's result set.

   value1, value2  Fixed values used to create column headings.

If any rows or columns contain NULLs they will appear under a row/column as <>

Examples

'Return Purchase Dates as rows, ServicePack pivoted as columns and count the numbers of servers from the table T_Servers:

TRANSFORM Count(T_servers.svr_Name) As CountServers
SELECT T_Servers.svr_Purchase_Date
FROM T_Servers
GROUP BY T_Servers.svr_Purchase_Date
PIVOT T_Servers.svr_os_Service_Pack;

“Some painters transform the sun into a yellow spot; others transform a yellow spot into the sun” ~ Pablo Picasso

Related:

Delete - Delete records.
Execute (SQL/VBA) - Execute a procedure or run SQL.
Insert - Add records to a table (append query).
Nz - Detect a NULL value.
Select - Retrieve data from one or more tables or queries.
Select Into - Make-table query.
Select-Subquery - SubQuery
Union - Combine the results of two SQL queries.
Update - Update existing field values in a table.
Q304458 - Create a crosstab query with multiple value fields


© Copyright SS64.com 1999-2013
Some rights reserved