Posted by DickChristoph on 03/02/06 01:33

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" <mike.a.rea@gmail.com> wrote in message
news:1141241610.678093.61540@v46g2000cwv.googlegroups.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.
>

[Back to original message]