cfoutput query group

[trojnfn]

I have a query that returns data displayed in table that looks like this :

Name ID Company count
Joe Smith xx1xx Burger Inc 1
Joe Smith xx1xx McDonalds 5
Bob Jones AA2AA Jets Inc 4

What I would like it to look like is this, display the name and ID only once :

Name ID Company count
Joe Smith xx1xx Burger Inc. 1
McDonalds 5
Bob Jones AA22A Jets Inc 4

This is the code that I am attempting to use, but it obviously is incorrect.

<cfoutput query="qryGet_Null" group="buyer_number">
<tr>
<cfoutput>
<td>#buyer_name#</td>
<td>#buyer_number#</td>
</cfoutput>
<td>#supplier_name#</td>
<td align="center">#total_count#</td>
</tr>
</cfoutput>

Where do I have to place the cfoutput tags to make the output displayed like the sample above ?

Also, if I export to excel, will it export just the way it is, with one name only, per supplier ?

Thanks

[CF_output]

For starters, dont place <cfoutput></cfoutput> tags within an output query... it wont work.

If you are going to have multiple instances of the same buyer with multiple companies.. and you have to run it like this, I would suggest running a query to return all of your results..

Then run an output query, and within the output query, run a query of queries to select the supplier name based on the id/name passed in the output query.

This is a basic example, you'll have to rewrite to match your needs etc.

<cfoutput query="main_query">
<cfquery name="companies" dbtype="query">
select * from main_query
where name = '#buyer_name#' <--- I would use an id to be more specific then a name --->
</cfquery>

<cfloop query="companies">
<tr>
<td>#buyer_name#</td>
<td>#buyer_number#</td>
<td>#supplier_name#</td>
<td align="center">#total_count#</td>
</tr>

</cfloop>

</cfoutput>

[insuractive]

Actually, trojnfn's nesting of <cfoutput> was just fine, since the outer <cfoutput> tag was using the group="" attribute.

When using the group attribute, you have to remember that everything inside the outer <Cfoutput> block will only loop once per instance of the grouped field. the code inside the inner <cfoutput> block will loop once for each record in your query.

However, this may not be the best tool to use in this situation, since it looks like you want multiple rows, but only display the name on the first row for a given buyer. Maybe something like this might work better for you:

<!--- Used to store the current buyer name --->
<cfset sHoldName = "">
<cfoutput query="qryGet_Null">
<tr>
<!--- Only display the buyer name if the buyer name has changed since the last record --->
<td><cfif buyer_name neq sHoldName>#buyer_name#<cfelse> </cfif></td>
<!--- Only display the buyer number if the buyer name has changed since the last record --->
<td><cfif buyer_name neq sHoldName>#buyer_number#<cfelse> </cfif></td>
<!--- List supplier name and total_count for all rows --->
<td>#supplier_name#</td>
<td align="center">#total_count#</td>
<tr>
<!--- Store the buyer name for comparison against the next record --->
<cfset sHoldName = buyer_name>
<cfoutput>