Dynamic Transposition - From Multiple Rows to One Row


I want to transfer the multiple rows in the student table into one row. The transposition result is shown as below. How do I do it?



The static SQL CASE WHEN can’t handle dynamic transposition. But programming a dynamic SQL workaround is complicated. SPL syntax is dynamic. It can do the transposition by directly using the xlsx file as a data source:



$select name,age,sex,kg   from student.xlsx






The data in the above student table is saved as student.xlsx:


Execute the above SPL script to get the desired result:


A1: Retrieve the original data;

A2: Join up the NAME value with every other field name in A1’s table sequence to get new field names and use them to create a new table sequence. Here macro is used to compose a dynamic statement;

A3: Concatenate the values of the last 3 fields in A1’s records into a sequence and populate its members into A2’s table sequence to get the desired result.