【Question】

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?

【Answer】

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:

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.