How to Concatenate Strings in a Specific Column in Each Group

Problem description & analysis

Database table orders has two fields – order IDs (ORDERID) and their descriptions (DESCRIPTION), as shown below:

ORDERID

DESCRIPTION

728123

WHITE

728123

35.00X45.00

728123

EXTRA

728123

SANDBLASTED OUTTER

728123

CHARCOAL FLAT

728123

SP

728123

BT

728123

NO GLAZE

728123

EXTRA STICKS

728125

WHITE

728125

56.00X24.00

We are trying to group the table by ORDERID and concatenate DESCRIPTION strings (separated them by comma). Below is the desired result:

ORDERID

DESCRIPTION

728123

WHITE,35.00X45.00,EXTRA,SANDBLASTED     OUTTER,CHARCOAL FLAT,SP,BT,NO GLAZE,EXTRA STICKS

728125

WHITE,56.00X24.00

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

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

3

=A2.group(ORDERID;~.(DESCRIPTION).concat@c():DESCRIPTIONS)

Explanation:

A1  Connect to demo database.

A2  Execute SQL query, return result as a table sequence, and automatically close database connection.

A3  Group A2’s table sequence by ORDERID, and connect to DESCRIPTION strings by comma.

Read How to Call an SPL Script in Java to learn about the method of integrating a SPL script with a Java program.

 

Q & A Collection

https://www.eclipse.org/forums/index.php/t/1092202/