Posted by Hugo Kornelis on 11/23/07 21:50

On Fri, 23 Nov 2007 05:52:13 -0800 (PST), Bart op de grote markt wrote:

>I have also tried out the solution below

Hi Bart,

Don't use this one - the "trick" it uses is undocumented, so it might
stop to work in a future release or even after applying a hotfix. In
fact, there have already been situations documented where it doesn't
work as expected (I unfortunately lost the URL though).

Use the FOR XML trick that J posted instead. However, because of his use
of SUBSTRING, the maximum length for the concatenation is limited to 100
characters (or whatever fixed value you use). You can solve that by
using STUFF instead of SUBSTRING:

SELECT test1,
STUFF((SELECT ', ' + test2 AS [text()]
FROM test AS t
WHERE t.test1 = ot.test1
AND t.test3 = ot.test3
FOR XML PATH(''), ELEMENTS), 1, 2, '') AS test2,
test3
FROM test AS ot
GROUP BY test1, test3;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]