Fast Way To Concat Records To Comma Sep VARCHAR

Hi All,

Here's a challenge.

If there is a 'one word' answer to this, i.e. the name of a built in
SQL Server function I can use, that would be great! However...

I have a standard 1:m relationship, e.g. tblHeader and tblDetail.

I want to create a view of records from 'tblHeader' and within that
view have a column (called e.g. DetailTypes) that provides a single
comma separated varchar of values from a varchar field (called e.g.
DetailType) that appears in related records in the 'tblDetail' table.
(hope you understood that :)

e.g. the contents of my 'tblDetail' table could be....

Detail ID | Header ID | DetailType
-----------------------------------
1 | 1 | A
2 | 1 | A
3 | 1 | B
4 | 2 | A
5 | 2 | C
6 | 3 | B

Therefore, the view I want to create should return:

Header ID | DetailTypes
-------------------------------
1 | 2A, 1B
2 | 1A, 1C
3 | 1B


i.e. the first row can be read as "Header 1 has 2 'A' detail records
and 1 'B' detail record."

I have created a view e.g. 'vqryHeaders' which calls a user defined
function that takes the HeaderID and opens a cursor on another view,
e.g. 'vqryDetailGrouped'. The other view groups the records in
tblDetail so that I can get a count of each DetailType for each Header
ID. The cursor then loops through the returned records concatenating
the count and detail type into a comma separated string (as shown
above).

However, when I run this across 20k records it is soooo sloooooww. I
have indexes on the relationship fields and I am using realistically
sized varchars - neither made any difference in speed. It is
definately the function that I wrote that slows it down as the view is
lightning fast when I remove my function call.

I can supply source code if necessary, but I think that this is a
kind-of generic problem so I don't see the point - yet.

I really hope you can help.

Regards,

Jezz