| Inverting a table using SQL [message #370735] |
Tue, 25 January 2000 19:13  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
