Transpose Multiple Columns into One Row in Each Group

 

Problem description & analysis

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

CLASS

NAME

SID

1

name1

001

1

name2

002

2

name3

003

2

name4

004

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:

A

1

=connect("oracle")

2

=A1.query@x("SELECT * FROM STAKEHOLDER")

3

=A2.group(CLASS;~.(NAME/""/SID).concat("   ")).(#1/" "/#2)

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.

Q & A Collection

https://stackoverflow.com/questions/64138971/how-to-merge-2-tables-into-1-row-with-multiple-entries-from-second-table-in-ora