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.

"Alan" <alanpltse (AT) yahoo (DOT) com.au> wrote

Try GROUP BY using the MAX aggregate on the times.

"Alan" <alanpltse (AT) yahoo (DOT) com.au> wrote

Try:

create table bucket (
bucketid int not null,
measure time not null,
level char(5) not null,
primary key (bucketid, measure, level),
check (level in ('Empty', 'Half', 'Full'))
);

insert into bucket values (1, '9:00' , 'Empty');
insert into bucket values (1, '10:00', 'Half');
insert into bucket values (1, '11:00', 'Full');
insert into bucket values (2, '9:20' , 'Empty');
insert into bucket values (2, '11:30', 'Half');
insert into bucket values (2, '13:00', 'Full');
insert into bucket values (3, '11:00', 'Empty');
insert into bucket values (3, '14:10', 'Half');
insert into bucket values (3, '16:00', 'Full');

select b0.bucketid, min(b1.measure), min(b2.measure), min(b3.measure)
from bucket b0, bucket b1, bucket b2, bucket b3
where
b0.bucketid = b1.bucketid and
b0.bucketid = b2.bucketid and
b0.bucketid = b3.bucketid and
b1.level = 'Empty' and
b2.level = 'Half' and
b3.level = 'Full'
group by b0.bucketid;

HTH
/Lennart
--
the above email no longer works due to spam.
values'lennart'||CHR(46)||'jonsson'||CHR(64)||'enl ight'||CHR(46)||'net'

This is a simple crosstab query. The SQL looks like this:

TRANSFORM First(tblBuckets.Status) AS FirstOfStatus
SELECT tblBuckets.BucketID
FROM tblBuckets
GROUP BY tblBuckets.BucketID
PIVOT tblBuckets.Time;

By the way, "Time" is not a good choice for a field name. It would be
better to call it "TimeCheck", or "StatusTime" or something that is more
obviously user-created.

HTH- Betsy