Outputting columns into list format

Question

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.

ilssac · Answer

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded