Query Grouping Problem

[Dynamo3209]

Hi,
I am pulling out some records from the database like ClientName, OrderID, ClientLocation, Productsordered.
I have to generate a report in coldfusion for total orders received by client name and location and give a breakup of products ordered.
Something like this

Client1 - Location1 TotalOrders Product1, Product2, Product3
Client1-Location2 TotalOrders Product1, Product2, Product3
Client2-Location1 TotalOrders Product1, Product2 Product3

I can get the query to display

client1 Location1 totalorders product1
client1 location1 totalorders product2
etc..
client2 location1 totalorders product1
and so on

but the problem is how do I display this in one row in a report format like I mentioned above

client1 location1 totalorders(for all products) product1(#of orders) product2(no of orders)

Thanks,
Dynamo

 

[r937]

make sure your query has ORDER BY client,location,product

then

<cfoutput query="q" group="client">
<!-- header for #client# if desired -->
<cfoutput group="location">
<!-- header for #location# if desired -->
<cfset products="">
<p>
<cfoutput>
<cfset products=ListAppend(products,#product#)>
</cfoutput>
<!-- footer for #location# -->
#client# #location# #totalorders# #products#<br />
</cfoutput>
</p>
<!-- footer for #client# if desired-->
</cfoutput>




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)