Posted by Erland Sommarskog on 11/15/05 14:24

Hennie7863 (hdenooijer@hotmail.com) writes:
> But as i read the post you said that this was not a rightful way to do
> this so i searched further for a better solution and i found on SQL
> Team :
>
> DECLARE @TextList Varchar(100)
>
> SELECT @TextList = COALESCE(@TextList + ' ', '') + CAST(Text AS
> varchar(8000)) FROM <Table> WHERE Seq = @iSeq
>
> RETURN (@TextList)
>
> The only problem i've is that i am using this in a function and i want
> to build <table> dynamically. I have to use a function because i'm
> using this in a query, like this:

This too is a solution which depends on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515. This article
is somewhat schizofrenic, since it first says "The correct behavior for an
aggregate concatenation query is undefined." and then goes showing when
it may work after all. Personally, I prefer to not rely on it at all.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]