How do I aggregate strings?

Hi all,

has anyone ever come to a solution in joining strings together in a comma separated fashion? Something like an aggregate e.g. SELECT X, JOIN(myStringColumn), FROM Y GROUP BY X? And all that in SQL Server 2000 (I know you can write your aggegates in SQL 2005)? It is a bit tricky to acheive something like this? Any ideas?

Good morning everyone

KKT

You can create a UDF to achieve the same.

Roji. P. Thomas
http://toponewithties.blogspot.com

What would the implementation look like? Something like a loop in all rows of the same group? I cannot imagine how to imlement something like that. Any ideas?

KKT

Try whether this will help...

CREATE FUNCTION dbo.ConcatBooks(PublisherName varchar(30))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Output varchar(8000)
SET @Output = ''

SELECT @Output =CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Publisher
WHERE PublisherName = @PublisherName

RETURN @Output
END
GO

SELECT DISTINCT State, dbo.ConcatBooks(PublisherName)
FROM Authors
ORDER BY State


Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

You can concatenate with a SELECT query in a UDF, which will be faster than a cursor-based approach - in most cases.

Beware that such a UDF will always slow down the query - perhaps not noticeable on small resultsets, but might be a problem on larger resultsets.

In SQL 2000, the concatenated list is limited to VARCHAR(8000) or NVARCHAR(4000). I presume SQL 2005 can use VARCHAR(MAX) or NVARCHAR(MAX).


CREATE FUNCTION dbo.ConcatString (@lookupkey INT)
RETURNS VARCHAR(8000)
AS

DECLARE @Return VARCHAR(8000)
SET @Return = ''

SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN '' ELSE ',' END + column
FROM table
WHERE key = @lookupkey
ORDER BY column

RETURN (@Return)
GO

thanks guys. Clever solution. I only have small tables to work with so performance should not be a problem

KKT

Just as a side note, the CASE expression can be replaced with

COALESCE(@Return+ ',', '') + col


Roji. P. Thomas
http://toponewithties.blogspot.com

quote:Originally posted by Adriaan
SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN '' ELSE ',' END + column
FROM table
WHERE key = @lookupkey
ORDER BY column

And again, if you let me, the ORDER is not guaranteed.

Roji. P. Thomas
http://toponewithties.blogspot.com

The world has shrunk - Anonymous<br />So will the query when SSP gurus touch it - Ram [<img src='/community/emoticons/emotion-5.gif' alt='' />][<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

What was it Roji - the ORDER BY is not guaranteed because the query is also doing the concatenation? or it that the ORDER BY probably works, although it shouldn't?

quote:Originally posted by Adriaan

What was it Roji - the ORDER BY is not guaranteed because the query is also doing the concatenation? or it that the ORDER BY probably works, although it shouldn't?

It is that the ORDER BY probably works, although it shouldn't.

Here we are using ORDER BY to specify the internal execution order, But ORDER BY only guarantees the order of the end result.

While we are at it, the concatenation technique itself, is undocumented and NOT foolproof and there are numerous cases where it will fail.




Roji. P. Thomas
http://toponewithties.blogspot.com

Both the approaches mentioned in the blog entry relies on th physical execution order (ie, the execution plan) and bound to fail.

Look at this blog entry, where the blogger himself realizes the flaws after presenting it as a nice technic.
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html

Also see this discussion.
http://groups.google.co.in/group/mi...o populate a variable&rnum=1#1cf9d4014ebee1d9


Roji. P. Thomas
http://toponewithties.blogspot.com

Yes Frank, but say you have a number of rows (P) from the parent table, and a number of rows (P*C) from the child table from which the column values must be concatenated.

When the client app does the concatenation, you have to return (P*C) rows.
When SQL Server does the concatenation, you have to return (P) rows.

There might well be a break-off point where SQL Server would spend more time returning (P*C) values rows than concatenating and returning (P) rows.

Unfortunately, there are valid scenarios where the string concatenation should be performed at the server side itself. For eg, a batch job that aggregates data, which obviously has no front end.

The only supported method to achieve the desired results is using a cursor or a loop.

An approach using the T-SQL update extension is anyways preferred over the approach discussed here.
Like

UPDATE Table
SET @string = @String + col

Note that the table is not actually getiing updated there.



Roji. P. Thomas
http://toponewithties.blogspot.com

Well, No Order guarantees and even that approach is not documented.

But the

UPDATE table
SET @var = col = @var

extension is documented.

Roji. P. Thomas
http://toponewithties.blogspot.com

You can find nice solutions here:
http://biblog.pl/?p=140