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

I need a SQL query that would return each athlete and the sports they
participate in:

John - hockey,swimming
Michael - football,baseball
Eric - swimming

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?

On Apr 3, 2:28 pm, "AdrianG" <adrian.grigo... (AT) altairtech (DOT) ca> wrote:
You need to break that out into more tables. Put the names in one
table, the sports in another table and a cross table in between. This
will be a many to many relationship and make your query a piece of
cake.

AdrianG (adrian.grigorof (AT) altairtech (DOT) ca) writes:
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 on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Apr 3, 5:00 pm, "Brad" <Brad.Marsh... (AT) Teksouth (DOT) com> wrote:
Thanks Brad - normalizing the data will surely help but still I can't
see a quick way of retrieving the concatenated "sports" fields. The
real data is a bit more complicated than the sample that I mentioned
but the idea is the same.

On Apr 3, 6:00 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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

AdrianG (adrian.grigorof (AT) altairtech (DOT) ca) writes:
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 &amp;.
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx