|
|
Posted by Bart op de grote markt on 11/26/07 08:19
On 23 nov, 22:50, Hugo Kornelis <h...@perFact.REMOVETHIS.info.INVALID>
wrote:
> 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
Ok,
Thank you for this warning and the extra comments! I will follow the
solution as suggested then :).
Kind regards,
Bart
Navigation:
[Reply to this message]
|