P.groupc( g:G , … ; F ,…; N ,… )
Description:
Perform row-to-column/column-to-row transposition on multiple columns of a table sequence.
Syntax:
P.groupc(g:G,…;F,…;N,…)
Note:
The function performs row-to-column/column-to-row transposition on multiple columns of table sequence/record sequence P; default is row-to-column transposition.
Group table sequence/record sequence P by grouping field/grouping expression g, whose name is G. N,… are names of the new columns. Put values of fields F,… in each group under corresponding new columns N,… in order while ignoring extra new columns.
Parameter:
|
P |
A table sequence/record sequence |
|
g |
The grouping field/grouping expression |
|
G |
Field names of the result set; by default, it is name of grouping field g |
|
F |
Field names of P; by default use all fields of P except for g,… |
|
N |
Names of new columns; by default, use sequence numbers to represent the column names |
Option:
|
@r |
Perform column-to-row transposition. Put values of fields F,… in each group under corresponding new columns N,… in order and discard rows where N values are empty |
Return value:
Table sequence
Example:
Transpose rows to columns:
|
|
A |
|
|
1 |
=file("t1.txt").import@t() |
Return a table sequence:
|
|
2 |
=A1.groupc(Country:COUNTRY;Label,Total;L1,T1,L2,T2,L3,T3,L4,T4,L5,T5) |
Group table sequence A1 by Country field and define new field name as COUNTRY; put values of Label field and Total field under corresponding new columns L1, T1, L2, T2, L3, T3, L4, T4, L5 and T5 in order and return the following result:
Ignore the extra columns L5 adn T5. |
|
3 |
=A1.groupc(Country;Label,Total;) |
Use sequence numbers as new column names as parameter N,… is absent, and use name of g field as parameter G is absent; and return the following result:
|
|
4 |
=A1.groupc(Country;;) |
Use all fields except for Country as parameter F,… is absent and retrun same result as A3. |
Transpose columns to rows:
|
|
A |
|
|
1 |
=file("t2.txt").import@t() |
Return a table sequence:
|
|
2 |
=A1.groupc@r(Country;Label1,Total1,Label2,Total2,Label3,Total3,Label4,Total4;Label,Total) |
@r option enables column-to-row transposition: group A1 by Country, put values of Label1, Total1, Label2, Total2, Label3, Total3, Label4 and Total4 fields in each group under corresponding new columns Label and Total in order and return the following result:
|
|
3 |
=A1.groupc@r(Country;;Label,Total) |
Use all fields of A1 except for Country as parameter F,… is absent and retrun same result as A2. |
Related concept: