|
|
Posted by AdrianG on 04/04/07 01:48
On Apr 3, 6:00 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> AdrianG (adrian.grigo...@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 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
Navigation:
[Reply to this message]
|