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

[Simon Hayes]

"Jeremy Pridmore" <go*********@pridmorej.freeserve.co.uk> wrote in message
news:9b**************************@posting.google.c om...

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

The fastest (and easiest) way would probably be in a front end application,
as this sort of thing is very awkward in pure SQL. You might find these
links useful for more information:

http://www.aspfaq.com/show.asp?id=2279
http://tinyurl.com/bib2

Simon

[louis nguyen]

The fastest way I know is to use the TSQL extension to the update
statement.
UPDATE table
SET @var = col = expression which manipulates col
WHERE condition

I won't go into the details because it's rather lengthy and my wife is
waiting for me... I've included sample code and the output. -- Louis

-----------------------------------------------------------------------------
create table #H (headerID smallint)
insert into #H values(1)
insert into #H values(2)
insert into #H values(3)

create table #D (detailID smallint,headerID smallint,detailType
char(1))
insert into #D values(1,1,'A')
insert into #D values(2,1,'A')
insert into #D values(3,1,'B')
insert into #D values(4,2,'A')
insert into #D values(5,2,'C')
insert into #D values(6,3,'B')

select
d.headerid,
cast(count(d.detailtype) as varchar)+cast(detailtype as varchar) as
detailtypes,
identity(int,1,1) as i
into #T
from #H as h
join #D as d
on h.headerid=d.headerid
group by d.headerid,d.detailtype
order by d.headerid,d.detailtype

select headerid,identity(int,1,1) as i
into #Cursor
from #T
group by headerid order by headerid

declare @i int, @text varchar(8000)
set @i=1
while exists(select * from #cursor where i=@i) begin

set @text=''
update #T
set @text=detailtypes=@text +',' + detailtypes
where headerid=(select headerid from #cursor where i=@i)

set @i=@i+1
end

select a.headerid,right(detailtypes,len(detailtypes)-1) as detailtypes
from #T as a
join
(
select headerid,max(i) as i from #T group by headerid
) as b
on a.i=b.i


OUTPUT
headerid detailtypes
-------- ------------------------------------------------------------
1 2A,1B
2 1A,1C
3 1B

[--CELKO--]

>> I think that this is a kind-of generic problem .. <<

It is generic in the sense that newbies who don't understand what
First Normal Form (1NF) mean keep posting for a way to do front end
displays and reports in the SQL backend. This comes from not having
worked with a client/server architecture before, so you want to see
everything for the app written in one monolithic program.

The only safe way is to use a cursor and reutrn to slow, procedural
programming instead of declarative SQL programming. Any kludge you
use will not port to another SQL product, will run like glue and will
be almost unpredictable.

[Jeremy Pridmore]

jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...

I think that this is a kind-of generic problem .. <<

It is generic in the sense that newbies who don't understand what
First Normal Form (1NF) mean keep posting for a way to do front end
displays and reports in the SQL backend. This comes from not having
worked with a client/server architecture before, so you want to see
everything for the app written in one monolithic program.

The only safe way is to use a cursor and reutrn to slow, procedural
programming instead of declarative SQL programming. Any kludge you
use will not port to another SQL product, will run like glue and will
be almost unpredictable.

Thanks for confirming that the world still has some complete w@nkers
in it.

1. Don't make assumptions. I'm not a newbie - as my post said I
already have an answer, albeit a slow one.
2. Don't make assumptions. I have worked on several successful C/S
apps.
3. Don't make assumptions. I don't necessarily want my app complied
into one large monolithic program. Also, If it is possible to create
a reusable function into which you can pass several parameters and get
a concatenated string, that procedure would be classed as 'generic'.
4. Don't make assumptions. I don't want it to port to any other
platform. Why do you think I posted in comp.databases.ms-sqlserver
and not in comp.databases?

I already have code that runs like glue, which is why I posted. I
would expect any *intelligent* person to give a reasonable response
(such as Louis) - not a rant.

Although an appropriate procedure can be created in 'front-end' code -
in my case i'm using vbscript (more fuel?), I would expect that
varchar 'lookup' and manipulation in the database would be quicker
than having to retrieve all the data into the front end and process it
there.

Oh, and lets not forget the most inportant thing - the reason why I
want to do this! It will give me a searchable field in a view to
which I can apply my 'generic' filtering procedures.

So, how is your snow coming along?

"You're not much use to me alive are you" CELKO?
- Bricktop.

La, la, la - I'm ignoring you now.

What was that? Whatever.

[Jeremy Pridmore]

lo************@hotmail.com (louis nguyen) wrote in message news:<b0*************************@posting.google.c om>...

The fastest way I know is to use the TSQL extension to the update
statement.
UPDATE table
SET @var = col = expression which manipulates col
WHERE condition

I won't go into the details because it's rather lengthy and my wife is
waiting for me... I've included sample code and the output. -- Louis

<snip>

Thanks Louis - you reminded me about temporary tables. That will go
along way in helping me - my problem probably lays with the fact that
I want to return this concatenated field in a view - not in a stored
proc, so the only way i can think of doing it is to use a function
that it aliased and pass in the primary key, select the records i need
in the function and then concatenate them.

Thanks again,

Jeremy

[Erland Sommarskog]

Jeremy Pridmore (go*********@pridmorej.freeserve.co.uk) writes:

lo************@hotmail.com (louis nguyen) wrote in message

news:<b0*************************@posting.google.c om>...

The fastest way I know is to use the TSQL extension to the update
statement.
UPDATE table
SET @var = col = expression which manipulates col
WHERE condition

I won't go into the details because it's rather lengthy and my wife is
waiting for me... I've included sample code and the output. -- Louis

<snip>

Thanks Louis - you reminded me about temporary tables. That will go
along way in helping me - my problem probably lays with the fact that
I want to return this concatenated field in a view - not in a stored
proc, so the only way i can think of doing it is to use a function
that it aliased and pass in the primary key, select the records i need
in the function and then concatenate them.

Beware that any "fast" solution to your problem will rely on
undocumented and undefined behaviour. The only safe way to this
in SQL is to it with a cursor or some other iterative method.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[WangKhar]

>> The fastest way I know is to use the TSQL extension to the update

statement.
UPDATE table
SET @var = col = expression which manipulates col
WHERE condition

Erland:Beware that any "fast" solution to your problem will rely on
undocumented and undefined behaviour. The only safe way to this
in SQL is to it with a cursor or some other iterative method.

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?

[louis nguyen]

I'll admit the code I posted is not standard sql. The extension to
the UPDATE statement is written about in Inside SQL Server 7.0 in the
brain teasers section. It was used to calculate running totals.

[Erland Sommarskog]

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.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[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*********@pridmorej.freeserve.co.uk> wrote in message
news:9b**************************@posting.google.c om...

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