Home » RDBMS Server » Server Administration » Inverting a table using SQL
Inverting a table using SQL [message #370735] Tue, 25 January 2000 19:13 Go to next message
Rich Holowczak
Messages: 1
Registered: January 2000
Junior Member
Hi folks:
I have a table that looks like the following:
SDATE      STATION   READING
20-DEC-99  101       32.4
20-DEC-99  102       32.1
20-DEC-99  103       32.7
21-DEC-99  101       33.1
21-DEC-99  102       33.1
21-DEC-99  103       33.3
...


I need a single SQL statment that will output:
SDATE      R101  R102  R103
20-DEC-99  32.4  32.1  32.7     
21-DEC-99  33.1  33.1  33.3
...

I consider this as "inverting" the table.
I've tried various combinations of DECODE
and something like the following:
SELECT a.SDATE,
       a.reading AS R101,
       b.reading AS R102,
       c.reading AS R103
FROM   mydata a, mydata b, mydata c
WHERE  a.sdate = b.sdate
  AND  b.sdate = c.sdate
  AND  a.station = 101
  AND  b.station = 102
  AND  c.station = 103

The main problem is the number of stations is
not known in advance.

Any tricks I might apply?

Thanks in advance!

Rich H.

----------------------------------------------------------------------

create table mydata (sdate DATE, station INTEGER, reading NUMBER);
insert into mydata values ('20-DEC-99', 101, 32.4);
insert into mydata values ('20-DEC-99', 102, 32.1);
insert into mydata values ('20-DEC-99', 103, 32.7);
insert into mydata values ('21-DEC-99', 101, 33.1);
insert into mydata values ('21-DEC-99', 102, 33.1);
insert into mydata values ('21-DEC-99', 103, 33.3);
Re: Inverting a table using SQL [message #370743 is a reply to message #370735] Fri, 28 January 2000 12:49 Go to previous message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
I you now the number of stations, then it is easy. I give you an example where you now the number of stations. When you write a function that first selects the amount of stations, and then creates a dynamic sql, you must be able to pack it.

ex:
select sdate
,SUM(decode(stations,101,reading,NULL)) R101
,SUM(decode(stations,102,reading,NULL)) R102
,SUM(decode(stations,103,reading,NULL)) R103
from mydata
group by sdate;
Previous Topic: Re: Using record_datatype as an input/output variable in procedures
Next Topic: Need to sum char field in SQLPLUS
Goto Forum:
  


Current Time: Sat Oct 24 04:16:48 CDT 2020