Alternate of Scalar Function for Comma Sepated Selection

I have 2 Tables as below,

1) Purchase_Invoice

a. PurchaseInvoiceID

b. SupplierName

c. BillNo

d. BillDate

2) Purchase_Invoice_Items

a. PurchaseInvoiceItemID

b. PurhcaseInvoiceID (FK to Purchase_Invoice Table)

c. ItemName

d. Quantity

e. Rate

Now I want to select all the records of Purhcase_Invoice table exactly once with one column at last containing comma separated Item name of particular PurhcaseInvoiceID as below

PurchaseInvoiceID|SupplierName | BillNo | BillDate | Items (Comma Separated Items)

Currently I am using Scalar Function which takes PurchaseInvoiceID as Argument and Returns Comma Separated Itemnames…

It works well but when number of records are large than performance is very poor.

Is there another way of doing same thing? By Join or By some System Function?

Nilesh

If you're using SQL2005, i'd use a CLR table valued funciton that would join the 2 tables and then format the results as you require.

While i've not done this myself i would guess that querying the data once and then manipulating the resultset would be your quickest solution.

HTH!