3 Replies Latest reply on Oct 30, 2009 10:07 AM by Lumpia

    Outputting columns into list format

    Lumpia Level 1

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

       

      numberfruits
      1apple
      2apple
      1orange
      1pear
      2pear
      3pear

       

      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.

        • 1. Re: Outputting columns into list format
          ilssac Level 5

          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.

          • 2. Re: Outputting columns into list format
            Dan Bracuk Level 5

            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.

            • 3. Re: Outputting columns into list format
              Lumpia Level 1

              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.