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

AdrianG (adrian.grigorof@altairtech.ca) writes:
> 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?

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