Row to fixed column

 

Example

There is the scores.xlsx file of the student score sheet, some of the data is shown in the figure below:

Now it needs to be converted into the format shown in the figure below, which should be listed in the order of Chinese, Maths, and English.

Write SPL script:


A

1

=T("E:/work/scores.xlsx")

2

=A1.pivot(No,Name;Subject,Score;"Chinese","Maths","English")

3

=T("E:/work/scores1.xlsx",A2)

A1 Read in the scores.xlsx file 

A2 Group by No and Name, row to column A1, Subject value as the new column name, Score value as the new column value, and the new column names are arranged in the order of "Chinese", "Maths", and "English".

A3 Save A2 to the file scores1.xlsx