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.
https://www.eclipse.org/forums/index.php/t/1092202/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
