Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Urgent help

Re: Urgent help

From: Betsy <Betwalk_at_sisna.com>
Date: Fri, 25 Jul 2003 07:10:41 -0600
Message-ID: <bfra9l$rpf$1@balder.stud.idb.hist.no>


As posted in reply to the OP's request on another board, this can be treated as a simple crosstab query. The SQL looks like this:

TRANSFORM First(tblBuckets.WaterLevel) AS FirstOfWaterLevel SELECT tblBuckets.BucketID
FROM tblBuckets
GROUP BY tblBuckets.BucketID
PIVOT tblBuckets.Time;

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

HTH- Betsy

"Alan" <alanpltse_at_yahoo.com.au> wrote in message news:3f1f1b9a_at_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 Fri Jul 25 2003 - 08:10:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US