Re: Transform Rows into Columns

felix666007_no_solicitation_at_yahoo.com (Felix) wrote in news:80c2794f.0310061522.10ffc6a8_at_posting.google.com:

> 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

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"

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 Received on Wed Oct 08 2003 - 16:15:02 CEST