Rotated Arrays enable the creation of a logical view of repeating data so that each column in the repeating segment appears as a unique row. A benefit to using rotated arrays is that aggregate functions, such as SUM, can be applied. The following is an example of a rotated array.
The record shown is an order summary.
Sales Order Summary
|
CustomerID |
Jan Sales |
Feb Sales |
Mar Sales |
April Sales |
May Sales |
June Sales |
|---|---|---|---|---|---|---|
|
CUST876762 |
$400 |
$50 |
$20 |
$90 |
$98 |
$85 |
By treating the sales_month array as a rotated array, results are returned as follows:
Sales Order Summary Rotated Array
|
CustomerID |
Month |
Sales |
CNXARRAYCOLUMN |
|---|---|---|---|
|
CUST876762 |
Jan |
$400 |
0 |
|
CUST876762 |
Feb |
$50 |
1 |
|
CUST876762 |
Mar |
$20 |
2 |
|
CUST876762 |
April |
$90 |
3 |
|
CUST876762 |
May |
$98 |
4 |
|
CUST876762 |
June |
$85 |
5 |
Returning data in this form provides two benefits. First, many tools, including Microsoft Access and Visual Basic, cannot use ODBC tables containing more than 255 columns. If a table contains a 400-element array, it cannot be imported into Microsoft Access. However, by rotating the array, Access reads the array as a single column, thereby allowing manipulation of data. This has the effect of normalizing nonrelational data.
The pseudo-column CNXARRAYCOLUMN displayed in the above example is returned whenever the rotated array feature is used. This column represents the element number of the array for this row of data.
Secondly, mathematical functions such as SUM, AVG, MIN, and MAX are easily applied to numeric data in column format. In the
following example, trying to return the sales average would be much more difficult without the rotated array.
Here is an example of an SQL statement used for a non-rotated array:
SELECT (JanSales + FebSales + MarSales + AprSales + MaySales + JuneSales + JulySales + AugSales + SeptSales + OctSales + NovSales + DecSales)/12 as SalesAverage from ORDER_SUMMARY
Here is the same example of an SQL statement used as a rotated array:
SELECT AVG(MonthSales) as SalesAverage from ORDER_SUMMARY
