Crosstab Example

The Crosstab option is best explained using an example. Consider the following matrix of information, where the first row contains column names:

Product Time Measurements
A 1/1/9911:00 AM 1.2
A 1/1/9911:05 AM 1.1
B 1/1/9911:00 AM 1.4
B 1/1/9911:05 AM 1.2
A 1/1/9911:07 AM 1.3
A 1/1/9911:09 AM 1.5
B 1/1/9911:07 AM 1.5
B 1/1/9911:09 AM 1.1

If the above matrix is a subset of records from a table in a database, it may be useful to create a query that returns a set of two columns, containing the observed measurements for products A and B, with the unique values of timestamps defining the rows. To accomplish this task, a crosstab query is defined. Three selections are available from the Crosstab drop-down list box:

Crosstab Column
Specifying a column in a query as a crosstab column indicates that its unique list of values defines the columns in the recordset returned. A crosstab query must contain at least one (and no more than one) crosstab column.
Crosstab Row
Specifying a column in a query as a crosstab row indicates that its unique list of values will be used to define the rows in the recordset returned. A crosstab query must contain at least one crosstab row. It may contain multiple crosstab rows. In the case of more than one crosstab row, the unique combinations of values in the crosstab rows will define the rows of the recordset returned.
Crosstab Value
Specifying a column in a query as a crosstab value indicates that its values will be used to populate the cells defined by the intersection of the crosstab row and crosstab column values. A crosstab query must contain at least one column defined as a crosstab value.

In the example above, specifying Product as a Crosstab Column, Time as a Crosstab Row, and Measurements as a Crosstab Value will return the following recordset:

TimeAB
1/1/99 11:00 AM1.21.4
1/1/99 11:05 AM1.11.2
1/1/99 11:07 AM1.31.5
1/1/99 11:09 AM1.51.1

Note. Special keywords are available for SQL statements related to crosstab queries. See the Special SQL Keywords topic for information about available keywords that may be used in the SQL statements in STATISTICA Enterprise.