Outputting columns into list format

Report · Oct 30, 2009

Quick question. I have 2 columns in SQL broken out like so:

number	fruits
1	apple
2	apple
1	orange
1	pear
2	pear
3	pear

I need a comma separated output like so:

1,2 apple

1 orange

1,2,3 pear

I've messed around with cf function ValueList and SQL Select like so:

<cfquery name="qFruits">
SELECT number, fruits

FROM table

WHERE (fruits = 'apple')
GROUP BY number, fruits

ORDER BY fruits
</cfquery>

<table
<tbody>
<cfoutput query="qFruits" group="fruit">
<tr>
<td>#valuelist(qFruits.number)#</td>
<td>#fruits#</td>
</tr>
</cfoutput>
</tbody>
</table>

and am able to get the individual fruit output:

1,2 apple

but not sure how to modify my code to get all fruits/numbers. Thanks.

Report · Oct 30, 2009

I would sugest that valueList() is the wrong option for what you are attempting to do. It looks like a clasic use case for the group property of the <cfoutput...> tag.

<cfquery name="qFruits">
  SELECT     number, fruits
  FROM         table
  WHERE     (fruits = 'apple')
  GROUP BY number, fruits
  ORDER BY fruits
</cfquery>

<table
<tbody>
<cfoutput query="qFruits" group="fruits">
<tr>
  <td><cfoutput>#qFruits.number#,</cfoutput></td>
  <td>#qFruits.fruits#</td>
</tr>
</cfoutput>
</tbody>
</table>

I leave it up to you and the documentation to figure out how to eliminate the extra comma this simple example will produce.

Report · Oct 30, 2009

Another way is to select a concatonation of the two fields, plus a comma in the query. This will probably include converting the number to a string. The details depend on the db.

Report · Oct 30, 2009

Dan,

My example uses the group tag in the cfoutput. Thanks however. I looked at my problem again and determined that I did not need to output my columns like that and tried a completely different method. Thanks for assisting however.