How best to merge rows into a comma separated field?

I have a report I am running against a Progress DB.  My goal is to have the items returned by my query show up in a field as comma seperated values.  



Students Table 

ID - Name

1 - Joe

2- Frank

3 - Susan

4 - Mary


In my report I would have an area with a display such as:

"Current Students: Joe, Frank, Susan, Mary"

In SQL Server I see there are some DB functions to merge the select items, but nothing shows up on the Progress side.  Suggestions on how I can accomplish this?  (ideally DB-neutral solution)?

robsil's picture
Joined: Jul 30 2010 - 5:28am
Last seen: 2 years 7 months ago

2 Answers:

Just an idea with a StringBuilder variable:

Create a new variable with name "MyStringJoiner" of Type java.lang.StringBuilder and set calculation type to NOTHING.

As InitalValueExpression you set: new StringBuilder() 

As VariableExpression you set: $V{MyStringJoiner}.append(",").append($F{Name})

Then you can create a new TextField in your summary band or any group footer band and set as TextExpression: $V{MyStringJoiner}.length() > 1 ? $V{MyStringJoiner}.substring(1):"<no items found>"

Here I used just a "AntiIndexOutOfBoundsException-Security" Inline-IfThenElse expression for the case that your query returns no names, then you can't subString the first "useless" comma.

So this solution is completely free of any database syntax and uses just JasperReports logic. Of course it iterates through all your resulting records. But you could delete the DetailBand if you just want to display the concated data in summary/groupfooter.

hth + regards from Dresden/Germany


C-Box's picture
Joined: Jul 19 2006 - 5:58pm
Last seen: 1 month 2 days ago

If you are directly using JasperReports library, why don't you just execute the sql query and concatenate column name in rows and just pass the concatenated value as a parameter to the report? You'll be able to show the concatenated value anywhere in your report that way.

hozawa's picture
Joined: Apr 24 2010 - 4:31pm
Last seen: 4 years 3 months ago