SQL query help

[AdrianG]

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?

[Brad]

On Apr 3, 2:28 pm, "AdrianG" <[e-mail]:

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?

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.

[Erland Sommarskog]

AdrianG (ad*************@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, es****@sommarskog.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

[AdrianG]

On Apr 3, 5:00 pm, "Brad" <[e-mail]:

On Apr 3, 2:28 pm, "AdrianG" <[e-mail]:

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?

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.- Hide quoted text -

- Show quoted text -

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.

[AdrianG]

On Apr 3, 6:00 pm, Erland Sommarskog <[e-mail]:

AdrianG ([e-mail]) 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 onhttp://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

[Erland Sommarskog]

AdrianG (ad*************@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 &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, es****@sommarskog.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