Re: SQL statement help please

From: programmer <int.consultNOCAPITALS_at_macmail.com>
Date: Fri, 1 Aug 2003 10:56:51 +0100
Message-ID: <bgde0c$59l$1@pheidippides.axion.bt.co.uk>

> I want to display the table as follows:
>
> VehicleID A B C D E
> ------------------------------------------------------------
> 1 9:00 9:10 9:20
> 1 9:30 9:40 9:50 10:00 10:10
>

Not possible in pure SQL, you'll need some procedural code. The nearest I could get was (in Oracle):

create table vehicles(VehicleID int, time date, Station char(16));

insert into vehicles values(1,to_date('0900','hh24mi'),'A');
insert into vehicles values(1,to_date('0910','hh24mi'),'B');
insert into vehicles values(1,to_date('0920','hh24mi'),'C');
insert into vehicles values(1,to_date('0930','hh24mi'),'A');
insert into vehicles values(1,to_date('0940','hh24mi'),'B');
insert into vehicles values(1,to_date('0950','hh24mi'),'C');
insert into vehicles values(1,to_date('1000','hh24mi'),'D');
insert into vehicles values(1,to_date('1010','hh24mi'),'E');

select v1.vehicleid,

 to_char(v1.time,'hh24:mi') as A,
 to_char(v2.time,'hh24:mi') as B,
 to_char(v3.time,'hh24:mi') as C,
 to_char(v4.time,'hh24:mi') as D,
 to_char(v5.time,'hh24:mi') as E

from vehicles v1, vehicles v2, vehicles v3, vehicles v4, vehicles v5

where v1.station='A'
and v2.station='B'
and v3.station='C'
and v4.station='D'
and v5.station='E'
and v1.time < v2.time
and v2.time < v3.time

and v3.time < v4.time
and v4.time < v5.time;

VEHICLEID A B C D E

1 09:00 09:10 09:20 10:00 10:10
1 09:00 09:10 09:50 10:00 10:10
1 09:00 09:40 09:50 10:00 10:10
1 09:30 09:40 09:50 10:00 10:10

Received on Fri Aug 01 2003 - 04:56:51 CDT