Transpose Multiple Columns into One Row in Each Group #eg15

Problem description & analysis

We have a database table STAKEHOLDER that has data as follows:

We are trying to group the table by CLASS and, for each group, combine all the other column values into one row. Below is the desired result:

1 name1 001 name2 002

2 name3 003 name4 004

Solution

We write the following SPL script p1.dfx within esProc:

Explanation:

A1   Connect to database oracle.

A2  Return a table sequence made up of query results and auto-close the database connection when query is finished.

A3   Group A2’s table sequence by CLASS and join values of multiple columns into one row.

Refer to How to Call an SPL SCript in Java to learn about how to integrate the SPL script with a Java program.

Comments

[Judy]

SPL open source address:github.com/SPLWare/esProc