Split and Extend Each Comma-separated Value to Multiple Rows

 

Problem description & analysis

Database table ORDERS has two fields ORDERNAME and LEVEL, as shown below:

ORDERNAME

LEVEL

order1

First,Second,Third

order2

Forth,Fifth,Sixth

We are trying to split each comma-separated LEVEL value and populate the parts to multiple rows, as shown below:

ORDERNAME

LEVEL

order1

First

order1

Second

order1

Third

order2

Forth

order2

Fifth

order2

Sixth

Solution

We write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

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

3

=A2.news(LEVEL.split@c();ORDERNAME,~:LEVEL)

Explanation:

A1  Connect to demo database.

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

A3  Loop through each row of A2. Split the LEVEL value into a sequence by comma and generate a row for each member. The new row will consist of two columns. The first is ORDERNAME and the second, named LEVEL, is the current member (represented by ~) of the split sequence of the LEVEL value.

Read How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.

Q & A Collection

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