pivot()
Here’s how to use pivot() functions.
A.pivot( g:G,…;F,V;N i :N' i ,… )
Description:
Perform row-to-column/column-to-row transposition for a table sequence/record sequence.
Syntax:
A.pivot(g:G,…;F,V;Ni:N'i,…)
Note:
The function performs a row-to-column transposition by default, or a column-to-row one. It groups table sequence/record sequence A according to grouping fields/expression g, converts values Ni in F field in each group into new columns and name them N'i, and reassigns values of V field to the new fields, during which G is a field result set and the default field name is that of g field.
Parameter:
|
A |
A table sequence/record sequence |
|
g |
Grouping fields/expressions |
|
G |
A field in the result set; default is g |
|
F |
A to-be-transposed field in the specified sequence |
|
V |
A field in the specified sequence |
|
Ni |
Values of F field and can be absent; by default it represents all unique field values in F |
|
N'i |
Names of new fields and default values are Ni |
Option:
|
@r |
Transpose rows to columns in table sequence/record sequence; In this case the default fields in Ni are the fields of A that are not included in g. Perform column-to-row transposition, where field names Ni are transposed as values of F field; when parameter N'i is present, it will be values of F field and values of original Ni field will be assigned to new field V. In this case the default values of Ni are all fields in sequence A except for grouping field g |
|
@s(g:G,…;F,f(V);Ni:N'i,…) |
Parameter f can be an aggregate function sum – count, max, min or avg, which supports syntax ~.f() where ~ references the current group; When N'I is present but Ni is absent, summarize V field in the Ni values that hasn’t been aggregated |
Return value:
Table sequence
Example:
Row-to-column transposition:
|
|
A |
|
|
1 |
=demo.query("select * from SCORES") |
Return a table sequence:
…
|
|
2 |
=A1.pivot(CLASS:ClassNew,STUDENTID:StudentID;SUBJECT,SCORE;"English":"new_English","Math","PE") |
Perform row-to-column transposition: group A1 by CLASS and STUDENTID, rename CLASS field and STUDENTID field in the result set ClassNew and StudentID respectively, use SUBJECT field values English, Math and PE as the new field names, during which English column is renamed ENGLISH, and redistribute SCORE column values into new columns.
|
|
3 |
=A1.pivot(CLASS,STUDENTID;SUBJECT,SCORE;"English","Math") |
As parameters G and N'I are absent, use g and Ni as column names in the result set.
|
|
4 |
=A1.pivot(CLASS,STUDENTID;SUBJECT,SCORE) |
As Ni is absent, use all unique SUBJECT field values by default.
|
Column-to-row transposition:
|
|
A |
|
|
1 |
=demo.query("select * from SCORES") |
|
|
2 |
=A1.pivot(CLASS:ClassNew,STUDENTID;SUBJECT,SCORE;"English":"new_English","Math","PE") |
Return a table sequence:
|
|
3 |
=A2.pivot@r(ClassNew:CLASS,STUDENTID;SUBJECT,SCORE;new_English:"English",Math,PE) |
Perform column-to-row transposition over A2: convert column names English, Math and PE as SUBJECT field values and transform values under the original columns ENGLISH, Math and PE into values under SCORE columns; CLASS is the new name of ClassNew column.
|
|
4 |
=A1.pivot(CLASS,STUDENTID;SUBJECT,SCORE) |
Return a table sequence:
|
|
5 |
=A4.pivot@r(CLASS,STUDENTID;SUBJECT,SCORE) |
Perform column-to-row transposition over A4: as Ni is absent, use all field names except for CLASS and STUDENTID by default.
|
Use @s option to use aggregate function at transposition:
|
|
A |
|
|
1 |
=demo.query("select * from employee") |
Return a table sequence:
|
|
2 |
=A1.pivot@s(DEPT;GENDER,avg(SALARY);"F":"female","M":"male") |
Perform row-to-column transposition: in each group, use average of F SALARY and M SALARY as values of the new columns, whose new names are female and male respectively.
|
|
3 |
=A1.pivot@s(DEPT;GENDER,max(SALARY);"F":"female",:"other") |
As Ni is absent, get the maximum value from all SALARY column values except for F.
|
|
4 |
=A1.pivot@s(DEPT;GENDER,max(SALARY);:"max") |
Get the maximum value from all fields.
|
|
5 |
=A1.pivot@s(DEPT;GENDER,~.(NAME).concat@c()) |
Use ~ to reference the current group; group A1 by DEPT, and in each group join up NAME values as a string and use it as values of F and M.
|
