Use the CROSSTAB command to count the occurrences of each unique pair of values from two columns in a table or view.
Options
AVG
Computes the numeric average. R:BASE rounds averages of INTEGER values to the nearest integer value and CURRENCY values to their nearest unit.
BY sidecol
Specifies the column whose values are displayed down the side of the crosstab.
cellcol
Specifies the column whose values are computed and displayed in the cells portion of the crosstab.
COUNT
Determines how many non-null entries there are for a particular column item.
(cellcol_exp)
Specifies the column whose values are computed and displayed in the cells portion of the crosstab with an expression. The expression can include columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.
FOR topcol
Specifies the column whose values are displayed across the top of the crosstab. If the topcol option is omitted, the sidecol and cellcol total or computation is displayed.
FROM tblview
Specifies the table or view.
MAXIMUM
Selects the maximum numeric, time, date, or alphabetic value in a column. For the TEXT data type, R:BASE evaluates the first 8 characters. For the NOTE data type, R:BASE evaluates the first 20 characters, but this permits text expressions from views to be used.
MINIMUM
Selects the minimum numeric, time, date, or alphabetic value in a column. For the TEXT data type, R:BASE evaluates the first 8 characters. For the NOTE data type, R:BASE evaluates the first 20 characters, but this permits text expressions from views to be used.
SUM
Computes the numeric sum.
topcol
Specifies the column whose values are displayed across the top of the crosstab.
=w
Sets the width, in number of characters, of the column displaying the results for cellcol.
WHERE clause
Limits rows of data. For more information, see WHERE.
About the CROSSTAB Command
You can use CROSSTAB from the R> Prompt from the Calculate menu in the "Data Browser" window in R:BASE for Windows. R:BASE sorts the information in ascending order and displays it in tabular form. You can also display the results of numeric calculations performed on the values in the two columns in a third column. The CROSSTAB command does not consider null values in its calculations.
Examples
Example 01:
The following command shows the net sum of the transactions for the sales representative whose employee identification is 102, ordered by transaction date.
CROSSTAB SUM NetAmount FOR EmpID BY TransDate FROM TransMaster WHERE EmpID = 102
The results look similar to the following:
TransDate | 102 (Total) 102
---------- | --------------- ---------------
03/15/2010 | $19,965.00 $19,965.00
---------- | --------------- ---------------
| $19,965.00 $19,965.00
Example 02:
The following command shows the average of the transactions for the customer whose identification number is 117.
CROSSTAB AVG NetAmount FOR CustID FROM TransMaster WHERE CustID = 117
CustID | (Total)
---------- | ---------------
117 | $6,337.50
---------- | ---------------
| $6,337.50
