Very Computer

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.

Quote:> 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.

Try GROUP BY using the MAX aggregate on the times.

SELECT BTEmpty.BucketID,
               BTEmpty.Time AS Empty,
               BTHalf.Time AS Half,
               BTFull.Time AS "Full"
FROM BucketTable AS BTEmpty
            INNER JOIN
            BucketTable AS BTHalf
            ON BTEmpty.BucketID = BTHalf.BucketID AND
                   BTEmpty.WaterLevel = 'Empty' AND
                   BTHalf.WaterLevel = 'Half'
            INNER JOIN
            BucketTable AS BTFull
            ON BTFull.BucketID = BTEmpty.BucketID AND
                   BTFull.WaterLevel = 'Full'

Regards,
jag

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)||'enlight'||CHR(46)||'net'

Quote:> > 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

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