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.