Dynamo3209
Programmer
Hi,
I am using the following code
and get the following output
however I want the following format
I am unable to accomplish this, I would appreciate any suggestions and help.
Thanks,
Dynamo
I am using the following code
Code:
<cfquery name ="Company_Totals" datasource="ABC">
SELECT Clients.ClientID, Clients.ClientName,
Customers.ClientID, Customers.CustID,
Clients.Location, count(Clients.ClientID) As TotalOrders,
Orders.Status, OrderDetails.ProductID,
Products.ProdName
from
Clients, `Orders`, Customers, OrderDetails, Products
Where
Clients.ClientID = Customers.ClientID And
Customers.CustID = Orders.CustID AND
OrderDetails.OrderID = Orders.OrderID AND
OrderDetails.ProductID = Products.ProductID
group by
Clients.ClientName, Clients.Location, Products.ProductID
</cfquery>
<cfquery name ="Order_Totals" datasource="ABC">
SELECT Clients.ClientID, Clients.ClientName,
Customers.ClientID, Customers.CustID,
Clients.Location, count(Clients.ClientName) As TotalOrders,
Orders.Status, OrderDetails.ProductID,
Products.ProdName
from
Clients, `Orders`, Customers, OrderDetails, Products
Where
Clients.ClientID = Customers.ClientID And
Customers.CustID = Orders.CustID AND
OrderDetails.OrderID = Orders.OrderID AND
OrderDetails.ProductID = Products.ProductID
group by
Clients.ClientName, Clients.Location
</cfquery>
<cfoutput query="Order_Totals">
#TotalOrders#
</cfoutput>
<table border = 1><tr><td>ClientName</td><td>Location</td><td>TotalOrders</td><td>Products</td></tr>
<cfoutput query="Company_Totals" group="ClientName">
<cfoutput group="location">
<cfset products="">
<p>
<cfoutput>
<cfset products=ListAppend(products,#ProdName#)>
</cfoutput>
<cfset ordertotals="">
<p>
<cfoutput>
<cfset ordertotals=ListAppend(ordertotals,#TotalOrders#)>
</cfoutput>
<tr>
<td>#ClientName#</td><td> #Location#</td><td> #ordertotals#</td><td> #products#</td></tr>
</cfoutput>
</p>
</cfoutput>
Code:
2 6
[u]ClientName Location TotalOrders Products [/u]
Ford Burlingame 1,1 8,10
Triad Loss 3,1,1,1 1,2,7,10
however I want the following format
Code:
ClientName Location TotalOrders Product1 Product2 Product3 .
Ford Burlingame 2 1 1
Triad Loss 6 2 3 1 ..
Thanks,
Dynamo