set variables from recordset

[ghanna1]

I have an SQL table that I store OrderID, ItemID, Qty.  There are 15 possible ItemID's (will never be more).  I need to output the order into a single table row that shows the orderID and the qty for each item.

For example:

<cfquery name="orders" datasource="xyz"

Select *

From solditems

Where orderid = 'Order1"

</query>

Would return something like:

Order1, Item3, 6

Order1, Item4, 7

Order1, Item18, 10

The table needs to look like:

<tr>

<td>#orderID#</td>

<td>#item1qty#</td>

<td>#item2qty#</td>

<td>#item3qty#</td>

<td>#item4qty#</td>

<td>#item5qty#</td>

<td>#item6qty#</td>

<td>#item7qty#</td>

<td>#item8qty#</td>

<td>#item9qty#</td>

<td>#item10qty#</td>

<td>#item11qty#</td>

<td>#item12qty#</td>

<td>#item13qty#</td>

<td>#item14qty#</td>

<td>#item15qty#</td>

</tr>

I'm at a loss trying to figure out how to match the row from the results of the record set into the right #itemXqty#.

Hope I explained this well and thanks in advance for pointers in the right direction!

Gary

[BKBK · Accepted solution]

You could do something like

<cfoutput><table border="1"><tr><th>#orders.orderID#</th></cfoutput>

<cfoutput query="orders">

    <td>#itemID##qty#</td>

</cfoutput>

</tr></table>

For a start, your query would be more efficient using "select orderID, itemID, qty" instead of "select *" (assuming, of course, that the table has more than the 3 columns).