I have three tables:

Table Race
RaceID RaceDescription
======= ================
1 100 Yard Dash

Table Racer
RacerId RacerName
======== =========
1 Freddy
2 Joey
3 Iggy

Table Lap
LapId RacerId RaceId LapNum LapTime
===== ====== ====== ====== ======
1 1 1 1 32.44
2 1 1 2 31.64
3 1 1 3 31.53
4 1 1 4 31.46
5 1 1 5 33.18
6 2 1 1 39.83
7 2 1 2 37.49
8 2 1 3 37.70
9 2 1 4 36.02
10 2 1 5 36.48
11 3 1 1 37.58
12 3 1 2 33.63
13 3 1 3 32.86
14 3 1 4 32.66
15 3 1 5 32.76


I want to do a query that will produce a result set that looks like:

LapNum Freddy Joey Iggy
====== ====== ====== ======
1 32.44 39.83 37.58
2 31.64 37.49 33.63
3 31.53 37.70 32.86
4 31.46 36.02 32.66
5 33.18 36.48 32.76

Note: I am using Sql Server 2000

Thanks for the help!

Felix

felix666007_no_solicitation (AT) yahoo (DOT) com (Felix) wrote in message news:<80c2794f.0310061522.10ffc6a8 (AT) posting (DOT) google.com>...

do a google search for 'pivot query'. I know how to do it with oracle.
dont know if the syntax is exactly the same for sql server. You
basically use a series of inline views to transform your rows into
columns.

felix666007_no_solicitation (AT) yahoo (DOT) com (Felix) wrote in
news:80c2794f.0310061522.10ffc6a8 (AT) posting (DOT) google.com:

Felix,

It depends on what you mean. If you know exactly what racers you've got,
you can simply create an SQL statement that returns the data you want.

SELECT lapnum,
SUM(CASE WHEN racerid = 1 THEN laptime ELSE 0.0 END) AS "Freddy",
SUM(CASE WHEN racerid = 2 THEN laptime ELSE 0.0 END) AS "Joey",
SUM(CASE WHEN racerid = 3 THEN laptime ELSE 0.0 END) AS "Iggy"
FROM lap
WHERE raceid = 1
GROUP BY lapnum

You can also replace the racerid's in the CASE clauses with something
like:
CASE WHEN racerid = (SELECT racerid FROM racer where Racername =
'Freddy')...

But still you've got to know all racers.

If you mean you want an arbitrary number of columns to be returned,
depending on the number of racers, the answer is 'NO, it can't be done'.
At least not using standard SQL. According to the SQL standard, the
number of columns in the result set must be predictable and known by
compile time.



Regards,
Jarl