Split string with delimiter into several fields

[tracktorist13]

I have a dataset from query like this:

+---------+---------+---------+-------------+| ColumnA | ColumnB | ColumnC   | ColumnD     |+---------+---------+-----------+-------------+| A1      | B1      | C11,C12   | D11,D12     |+---------+---------+-----------+-------------+| A2      | B2      | C21,C22   | D21         |+---------+---------+-----------+-------------+

I want to split it like following:

+=========+=========+=========+=========+| ColumnA | ColumnB | ColumnC | ColumnD |+=========+=========+=========+=========+| A1      | B1      | C11     | D11     |+---------+---------+---------+---------+| A1      | B1      | C12     | D12     |+---------+---------+---------+---------+| A2      | B2      | C21     | D21     |+---------+---------+---------+---------+| A2      | B2      | C22     | -       |+---------+---------+---------+---------+

The amount of such substrings in columns C,D is unknown. There are about 15 columns like columns C,D.

I've tried to do like this (for the first row)

$F{OV9}.split(";")[0][/code]

and (for the second)

$F{OV9}.split(";").length > 1 && $F{OV9}.split(";")[1].length() >2 ? $F{OV9}.split(";")[1].trim() : "-"[/code]

Then hide the second row if there is no data in it. But it's fine only for case when you know exact amount of substrings.

Then I've tried to add table element with dataset:

new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource(java.util.Arrays.asList($F{OV9}.split(",")))[/code]

And $F{_THIS} field in table.

It works fine. But how to repeat rows from A,B columns? I can add them to the table and get problems with height of rows and amount of repeats.

Re-writing sql is a bad variant - it will be to slow. 

 

How can I do it?

[C-Box]

You could place a subreport instead a TableComponent below your first row and pass the (current) values from ColA and ColB as SubReportParameter. 

The DataSourceExpression could be your splitting expression you already wrote successfully. In your SubReport design you now repeat the "fixed" parameters passed for colA and colB from outside and use the splitted field values from colC and colD. So the Table should be continued by the SubReport by the dynamic splitting columnsC and columnsD.

of course not tried  but should work

hth + regards

C-Box