Fast Way to Concat Records to Comma Sep VARCHAR

[Jeremy Pridmore]

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. 'vqryDetailGrou ped'. 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

[WangKhar]

Thats interesting, thanks.

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

WangKhar (Wa******@yahoo .com) writes:

Erland - are you referring to the code above here? What do you mean
exactly? Is there some sneakiness of undocumented goodness you are
keeping from us?

No, the problem is that you have no guarantee that the code will always
produce the expected result. A change of query plan or whatever. Could
break with the next service pack.

Here is a KB article on a similar trick. Pay particular attention to
first paragraph under CAUSE:
http://support.microsoft.com/default.aspx?scid=287515.

[tperovic]

Does this help:
http://www.sqlteam.com/item.asp?ItemID=2368

"Jeremy Pridmore" <go*********@pr idmorej.freeser ve.co.uk> wrote in message
news:9b******** *************** ***@posting.goo gle.com...

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. 'vqryDetailGrou ped'. 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