Posted by figital on 03/02/06 16:07

If you are pushing this through some sort of parser, grid, asp page,
etc., here's what I usually do...

1) Use correlated tables with two datasets. Google search for
that--some of the .net controls support it via nesting.

2) Return everything on every record. e.g.
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-1),
qty, price, description
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-2),
qty, price, description
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-3),
qty, price, description

Then when you display this, just check neighbor rows (pseudocode):

For Each Row
If PreviousRow.InvoiceNumber <> CurrentRow.InvoiceNumber Then
Output Header Row (invoicenum, date, cust num)
Output Detail row(item number, qty, price, description)
Else
Output Detail row(item number, qty, price, description)
End If
Next

Sure this approach returns some data you won't use for every row but it
is very easy to do, and involves just one record set.

You could presumably implement this approach or DickChristoph's idea in
a cursor, too.

[Back to original message]