For dynamic row-to-column transposition, the to-be-generated field(s) cannot be predefined but can only be determined according to values of the corresponding original field.

Below is Employee table:

One task is to find average salary of each department in different areas, whose names are uncertain, and the desired result is as follows:

The task involves a row-to-column transposition, during which the names of target fields need to be extracted from the existing field. We can use the pivot() function to handle such dynamic transposition. The function can automatically extract names for the target fields when the target fields are not specified.

SPL script:

A1 Connect to the data source.
A2 Get average salary of each department in each area from Employee table grouped by department and area.
A3 Use pivot() function to perform row-to-column transposition; here the target fields are not specified.