Of the available aggregate functions available, array is unique in that it operates on both text and numeric values. Rather than perform a mathematical operation on collapsed rows, all values in the reference column (the column to which array is applied) are temporarily stored. As the unique single row is created, array assembles the reference column data from the set into a single, comma-separated string within the column.
Excluding the column to which the array is being applied, all rows are examined column-by-column in order to find identical rows. The header of the reference column receives a name change to become “Array of <column name>”.
Examples
The small dataset below will be used to show how array operates.
| Column A | Column B | Column C |
|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
Example 1
Applying the array function to Column C reduces the row count from four to two. The value in the column Array of Column C shows the sum of of the Column C values in the duplicate rows that were collapsed during the operation.
| Column A | Column B | Array of Column C |
|---|
| 1 | two | 5.0, 6.0, 4.0 |
| two | two | 7.0 |
Example 2
Applying the array function to Column A results in no row count reduction since examination of Column B and Column C values in each row reveals that each is already unique. The value in the column Array of Column A therefore displays each row with its original value—however, numbers have been converted into text.
| Array of Column A | Column B | Column C |
|---|
| 1.0 | two | 5 |
| two | two | 7 |
| 1.0 | two | 6 |
| 1.0 | two | 4 |
日本語
