Outputting multiple records in one table row

I am basically trying to do this but i dont know how to go about doing it. I have a table full up part information. Each part belongs to a category. I want to output the part info grouped by category so that the first column of the table shows you the recordcount of how many parts belong to the category, the second column gives you the partno's, with each partno on a new line. Kind of like 3 rows but in one table row. Anybody know if this is possible using cold fusion?
dmatthewsAsked:
Who is Participating?
 
CF_SpikeConnect With a Mentor Commented:
Assuming your query looks like this:

<CFQUERY DATASOURCE="tests" NAME="qParts">
SELECT categoryname,partnum,partinfo
FROM parts
</CFQUERY>

The code below should produce a table formatted the way you want.

<TABLE BORDER="1">
<CFOUTPUT QUERY="qParts" GROUP="categoryname">
<TR>
     <TD COLSPAN="3">#qParts.categoryname#</TD>
</TR>
<TR>
     <TD>Part Count</TD>
     <TD>Part Number</TD>
     <TD>Part Info</TD>
</TR>
<TR>
     <!--- initialize a counter so we know how many rows to span with the left hand cell--->
     <CFSET count = 0>
     <!--- initialize the grouped cells to an empty row for this category --->
     <CFSET groupedcells = "">
     <CFOUTPUT>
          <!--- increment the counter --->
          <CFSET count = count + 1>
          <!--- build the HTML string for the grouped cells --->
          <CFSET groupedcells = groupedcells & "<TD>#qParts.partnum#</TD><TD>#qParts.partinfo#</TD></TR><TR>">
     </CFOUTPUT>
     <!--- use the count variable to determine the rowspan for this category --->
     <TD ROWSPAN="#variables.count#">#variables.count#</TD>
     <!--- ouput the HTML string for the grouped cells --->
     #variables.groupedcells#
</TR>
</CFOUTPUT>
</TABLE>

Spike
0
 
YogCommented:
<cfoutput group="categoryname" name="yourqueryname">
<tr>
<td rowspan="#totalnoofrecords#">#recordcount#</td>
<cfoutput>
<td>#partno#</td>
</cfoutput>
</tr>
</cfoutput>

cheers.
0
 
CF_SpikeCommented:
What database are you using?

Spike
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dmatthewsAuthor Commented:
I am using an ingres database
0
 
dash420Commented:
I don't know why u are getting problem. I think because of No of record
u are getting problem. if that is case break the query. First get the record count
i.e.

<cfoutput>
<cfquery name="qryTest" datasource="dsn">
   select count(*) cnt, category _id
   from table
   group by category_id
</cfquery>

<table>
    <cfloop query="qryTest">
       <tr>
           <td> #cnt# </td>
           <cfquery name="qryTest1" datasource="dsn">
              <!---
                   Query to Get details by passing the category_id
              --->
              Select * from table
              where category_id = #category_id#
           </cfloop>
           <td>
              <table>
                  <cfloop query="qryTest1">
                     <tr>
                         <td>your second query fields</td>
                     </tr>
                  </cfloop>  
              </table>
           </td>
       </tr>  
    </cfloop>
</table>
</cfoutput>

I think this will solve ur problem, if not let me know what is ur problem briefly.
0
 
dmatthewsAuthor Commented:
The data comes from 3 tables. 1st table has the partno, part descr, and qty. The 2nd table is a master table for all components and is related to the first through partno. Also the categorycode for the part is in the 2nd table. So the part info is in the first table and the categorycode for the part comes from the 2nd table.
0
 
dmatthewsAuthor Commented:
Sorry, i meant 2 tables.
0
 
dmatthewsAuthor Commented:
Works like a charm. all i have left to do now is to remove the borders from with in rows. Thanks!!!!!
0
All Courses

From novice to tech pro — start learning today.