Re: Urgent help

Hi Betsy,

TRANSFORM / PIVOT is not SQL. It is a vendor-specific extension to the language.

Bob

"Betsy" <Betwalk_at_sisna.com> wrote in message news:bfra9l$rpf$1_at_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 - 15:57:42 CEST