>Is there a SQL statement that can take a one-to-many relationship between two tables and create a single table. The single table would be distinct yet contain combined data from the many-table.
>
>Example:
> Invoice table with one record per invoice and line item table with multiple line items.
>Resulting table has invoice data and line items combined in one field (possible memo).
>
>Thanks

Bill;



An approach may be to:
1. Create a select statement:

Select Invoice.InvPK,  Detail.LineItem, Detail.Quantity, Detail.Price ;
     from Invoice, Detail ;
     Where Invoice.InvKK = Detail.Inv_FK ;
     Into Cursor NewDetail

2. Create a scan with each field assigned to a variable within the scan.

3. Concatenation each row within the scan.

4. Append or Insert the new record into the table (memo field)

Perhaps something like this:

Local = lcDetail
lcDetail = “”

Use NewTable In 0
Select Newdetail

Scan
	lcDetail = NewDetail.LineItem
        lcDetail = lcDetail + "," + NewDetail.Quantity
        lcDetail = lcDetail + "," + NewDetail.Price
	
        INSERT INTO NewTable (AllDetail) ; 
        VALUES (lcDetail) 

EndScan

You should end up with a comma delimited string in this case.
Warning: I have not tried this so have fun!

Tom