Re: Transform Rows into Columns

From: Jarl Hermansson <jarl_at_mimer.com>
Date: 8 Oct 2003 14:15:02 GMT
Message-ID: <Xns940EA6962E372jarl@195.58.103.121>

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"

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