Displaying All Query Results in One String

[tranteri]

I'm making a report in iReport and I need it to list all the values of one field from a query in one text field on the report. The query returns all of the rooms in a building that meet a certain requirement, and I just need all of those rooms separated by a comma in one field instead of having them display like a vertical list (what it does if I just make a text field with the field from the query as an expression).

ie. I want it like this "3100, 3102, 3104, etc."

and not this:

3100

3102

3104

I'm fairly new to iReport and I've taken a look through this forum to find the answer but I couldn't find it. Anyone know how to do this?

 

Thanks!

[cbarlow3]

I think you probably want to do that transformation within SQL rather than in iReport. Anthony Molinaro's SQL Cookbook (c. 2006 O'Reilly) shows techniques for this in his section 6.10 "Creating a Delimited List from Table Rows".  In this particular case, the solution is more SQL-flavor-specific than usual, depending on whether you are using DB2, MySQL, Oracle, PostgreSQL, or SQL Server (the author give solutions for every "recipe"/problem in all these variations).  What kind of database are you using (and what version number, if you happen to know that too)?

Carl

[tranteri]

Hi Carl,

Thanks for the reply. I'm using a MySQL database version 5.1.41 and phpMyAdmin. I'll see what I can find out about creating a delimited list from table rows in SQL.

 

Cheers,

 

Ian

[cbarlow3]

The example in the book (which I highly recommend) assumes that you originally have a result set with columns DEPTNO and EMPS that originally might look like:

DEPTNO  EMPS
------  -----------
    10  CLARK
    10  KING
    10  MILLER
    20  SMITH
    20  ADAMS
    ...etc.
   
and you want to transform the output to this...
   
DEPTNO  EMPS
------  -------------
    10  CLARK,KING,MILLER
    20  SMITH,JONES,SCOTT,ADAMS,FORD
    ...etc.

You can doubtless modify the solution to meet your specify (possibly simpler) need.  Here's his MySQL solution (you're in luck--the MySQL version happens to be the simplest solution):

Use the built-in function GROUP_CONCAT to build the delimited list:

select deptno,
  group_concat(ename order by empno separator ',') as emps
  from emp
 group by deptno

Personally, I use DB2 way more than MySQL, so I don't know the particulars of how this function works, but just knowing the name of the function you need should help if you Google "MySQL GROUP_CONCAT".  Here's one article I found:

http://www.dougboude.com/blog/1/2009/12/A-SWEET-Little-MySQL-Function-GroupConcat.cfm

Hope that helps.

Carl

[tranteri]

Thanks, it worked with just a small change. When I tried it your way I was just getting "BLOB" back. I found out that was because the GROUP_CONCAT function needs characters entered, not integers. Anyway, I cast my room number integers to char and it works great!

 

Here's my code:

SELECT GROUP_CONCAT( CAST( room_no AS CHAR ) ) AS rooms

FROM licensed_rooms

WHERE lic_num =3188

 

Thanks again