string collection

[psivyer]

In the original table each order may have multiple invoice numbers - example .......
TABLE ORDERS
order_no order_seq inv_no
1234 1 I14567
1234 2 I23454
4567 1 A34567
7894 1 2348900
7894 2 4459888GH
7894 4 aa234fg
I would now like to create a table taking data from TABLE ORDERS whereby you would have one order line and the inv_no's would be concatenated - example taken from above data .......

MYTABLE

order_no inv_no
1234 I14567,I23454
4567 A34567
7894 2348900, 445988GH, aa234fg

Is this possible and how - your help much appreciated

Phil

 

[Catadmin]

Question: Is it always going to be a limited # of invoices or can it be any # of invoices? The answer will change the code solutions people here give you.

For a finite # of invoices, you just need to join your table back to itself for the number of times you need the invoice repeated.

Select t1.Order_no, t1.Invoice_no, t2.Invoice_no
from [Table Orders] t1
join [Table Orders] t2
on t1.Order_no = t2.Order_no

The above works if you only have 2 invoices for each order. Do a third and fourth join if you can only have up to 3 or 4 invoices on the order. Use Left Outer joins if some might have more and others have less.

However, if you could potentially have > 4 or unlimited invoices, then I would recommend using Temp Tables to go through every possible permeatation until you get your file record set and then do a SELECT ... INTO for the MyTable result.

The higher your number of invoices, the harder it is going to be and the more complicated the code.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"