Can I do this in SQL Analyzer or with a dts package?

[mike]

I am not sure how to approach this problem or if I can even do it
within SQL.

Say I have 2 tables invoice_hdr and invoice_dtl. I want to pull
specific fields from both tables, I want the data to be grouped
together however I want the information to be on different lines. One
for the information from the header and one for each corresponding
detail record.

So my output might look like

Invoice_number (1), Invoice_date, Customer_Number
Item_number (1-1), qty, price, description
Item_number (1-2), qty, price, description

Invoice_number (2), Invoice_data, Customer_number
Item_number (2-1), qty, price, description
Item_number (2-2), qty, price, description
Item_number (2-3), qty, price, description

etc..
So the invoice_number (1) is my first invoice and item_number (1-1)
would be the 1st item on tins invoice, etc...

My ultimate goal is to export this to a text or csv file.
Thanks.

[DickChristoph]

Hi Mike,

It can be done in a SQL stored proc but it will get kind of ugly. Basically
it sounds like you are taking two tables and trying to return one result set
(with 1 schema) or maybe just one text (varchar) column with all the data
written and concatenated as text.

Using VB Script, or a console application written in C, VB.Net or something
you could have your stored proc return two result sets in Invoice Number
order and then create the text (CSV file) by marching through the two
recordsets (if you are using ADO) and outputing records to your text file in
the application that calls the stored proc.

If you use VB Script you could put that in a DTS Package, Call the stored
proc and create the output file.

What is the schema of invoice_hdr and invoice_dtl?

-Dick Christoph
"mike" <mi********@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

I am not sure how to approach this problem or if I can even do it
within SQL.

Say I have 2 tables invoice_hdr and invoice_dtl. I want to pull
specific fields from both tables, I want the data to be grouped
together however I want the information to be on different lines. One
for the information from the header and one for each corresponding
detail record.

So my output might look like

Invoice_number (1), Invoice_date, Customer_Number
Item_number (1-1), qty, price, description
Item_number (1-2), qty, price, description

Invoice_number (2), Invoice_data, Customer_number
Item_number (2-1), qty, price, description
Item_number (2-2), qty, price, description
Item_number (2-3), qty, price, description

etc..
So the invoice_number (1) is my first invoice and item_number (1-1)
would be the 1st item on tins invoice, etc...

My ultimate goal is to export this to a text or csv file.
Thanks.

[figital]

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.