13.1 Row-to-column conversion for fixed columns


Here below is a data table:


Now we want to convert the table to the form as shown in the figure below, and list the scores in the order of Chinese, Maths and English:



=spl("=E(?1).pivot(ID,Name;Subject,Score; ""Chinese"",""Maths"",""English"")",Sheet1!A1:D13)

Perform the row-to-column conversion based on columns ID and Name. The values in the Subject column are transferred and used as the new column names, the values in the Score column are transferred and used as the values in the new columns, and the new column names are arranged in the order of “Chinese”, “Maths”, “English”.

esProc Desktop and Excel Processing
12.12 Dynamic association operation
13.2 Convert row-based table to crosstab