On Apr 3, 6:00 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
Quote:
AdrianG (adrian.grigo...@altairtech.ca) writes:
Quote:
I am trying to write a single SQL query that would retrieve the data
that I need. For example, I have a table called Athletes that has 2
fields: name and sport containing the name of an athlete and the
sports that he or she participates in. Some sample data:
John,hockey
Michael,football
John,swimming
Eric,swimming
Michael,baseball
>
Quote:
I need a SQL query that would return each athlete and the sports they
participate in:
>
Quote:
John - hockey,swimming
Michael - football,baseball
Eric - swimming
>
Quote:
I've tried all sorts of joins and aggregate functions but with no
success. I want to avoid to run a query listing the athletes and then
doing a query for each of them to get the sports. Can anyone provide
some tips on doing this with just one SQL query?
>
It's indeed not a trivial problem, as there is no direct function fot
this in SQL Server. SQL Server MVP Anith Sen has a couple of methods onhttp://www.projectdmx.com/tsql/rowconcatenate.aspx.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -
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.
Thanks again!
Adrian
