Posted by Erland Sommarskog on 04/04/07 22:03

AdrianG (adrian.grigorof@altairtech.ca) writes:
> Right on, Erland! The page compiled by Anith Sen was exactly what I
> was looking for - concatenation of row values. I tried the first
> approach (Dynamic SQL) but while it worked for a small number of
> records, it failed ( Server stack limit has been reached.) against a
> larger number (i.e. 20,000 records which is not really that much).
> However, the blackbox XML method worked like a charm. Here is a
> slightly modified version (to only show distinct sports and to remove
> the trailing spaces) that worked quite fast:
>
> SELECT p1.name,
> ( SELECT distinct RTRIM(sport) + ', '
> FROM Athletes p2
> WHERE p2.name = p1.name
> ORDER BY RTRIM(sport) + ', '
> FOR XML PATH('') ) AS sports
> FROM Athletes p1
> GROUP BY name ;
>
> Now, the real database is more complex than this example but it is
> surely a great start.

Great to hear that you got a solution.

I don't recall if Anith discusses this, but there is a small problem
with the method above: if the data has charcaters that are special to
XML, they will be itemised. For instance an ampersand will become &.
There are some more or less ugly methods to habdle that, but I don't
recall the details at the moment.



--
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]