Urgent help

From: Alan <alanpltse_at_yahoo.com.au>
Date: Wed, 23 Jul 2003 23:34:51 GMT
Message-ID: <3f1f1b9a@news.comindico.com.au>

I have the following table BucketTable :

BucketID Time WaterLevel

---

1                9:00    Empty
1                10:00    Half
1                11:00    Full
2                9:20    Empty
2                11:30    Half
2                13:00    Full
3                11:00    Empty
3                14:10    Half
3                16:00    Full

This table keeps the timestamp of the water level of the buckets. I want to write a SQL statement to show the following :

BucketID Empty Half Full

1                9:00        10:00    11:00
2                9:20        11:30    13:00
3                11:00       14:10    16:00

I tried

Select BucketID,

            IF(WaterLevel='Empty', Time, ''),
            IF(WaterLevel='Half', Time, '')
            IF(WaterLevel='Full', Time, '')

FROM Bucket
ORDER BY BucketID;

However, it did not work as it gave me 9 records rather than 3. I also tried DISTINCT and GROUP BY, it gave me 3 records but only with the Empty column has time. Received on Wed Jul 23 2003 - 18:34:51 CDT