Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Columns based on Query(Oracle10G 10.2.0.3.0)(Oracle10G 10.2.0.3.0)
| Dynamic Columns based on Query(Oracle10G 10.2.0.3.0) [message #540656] |
Wed, 25 January 2012 03:57  |
 |
Ramses800
Messages: 12
Registered: January 2012
Location: Sweden |
Junior Member |
|
|
I have a question about how to query a Oracle 10.2 database.
The data is in 2 tables and the the first table contains metadata about the actual data in the second table.
Now, normally this would just be a join of some sorts but the thing is that I need a rotated result with column headers from the first table displaying the data in the second table. To make things even more complicated, its all dynamic and subject to change.
Here is the example data
Table 1 SIGNALS
This table contains information about a dynamic number of signals
SIGNAL_ID SIGNAL_NAME
1 AAA
2 BBB
3 CCC
.. ..
and so on and so on
165 XZYZZ
At any point could new rows in table SIGNALS be added, deleted or modified
Table 2 SIGNAL_DATA
DATETIME SIGNAL_ID VALUE
2012-01-01 00:00:00 1 56
2012-01-01 00:00:00 2 871
2012-01-01 00:00:00 3 0.5
2012-01-01 01:00:00 1 57
2012-01-01 01:00:00 2 987
2012-01-01 01:00:00 3 0.76
and so on .. ..
The data in SIGNAL_DATA will grow every hour with new records for each signal defined in the table SIGNALS
Desired result of the query(based on user input for datetime)
DATETIME AAA BBB CCC
2012-01-01 00:00:00 56 871 0.5
2012-01-01 01:00:00 57 987 0,75
and so on .. .. ..
If the data in the first table would be static it would be fairly easy to write a lengthy but working query.
select distinct(h0.datetime) datetime,
(select value from signal_data h2 where h2.datetime=h0.datetime and h2.signal_id=1) AAA,
(select value from signal_data h3 where h3.datetime=h0.datetime and h3.signal_id=1) BBB,
(select value from signal_data h4 where h4.datetime=h0.datetime and h4.signal_id=1) CCC,
-- AND SO ON for each signal
from signal_data h0
where h0.datetime between start_date and end_time
and h0.signal_id in(select signal_id from signals)
I have been considering using a dynamic sql to create the query on the fly and the run it as well but the problem is that my client is somewhat limited(ie only capable of running normal queries etc) Again, if the signals where static then it would be possible to write a function that returned a TABLE type with a definitions matching the query. But as there is no way to know beforehand that definitions the best I have so far succeeded in is returning a REF CURSOR. Unfortunately, my client does not know how to parse/fetch a cursor so no help there.
Most grateful for any help or insight!
|
|
| | | |
| Re: Dynamic Columns based on Query(Oracle10G 10.2.0.3.0) [message #540737 is a reply to message #540684] |
Wed, 25 January 2012 08:38  |
|
Ramses800
Messages: 12
Registered: January 2012
Location: Sweden |
Junior Member |
|
|
I can not modify the client that request the data from the database. It is a compiled binary from third party vendor. It accepts standard queries as input(meaning that I write a query and the client executes it). It itself cannot execute dynamic SQL but it is possible to call a Oracle function/procedure that accepts parameter from the client.
Test Case
CREATE TABLE SIGNALS
(
SIGNAL_ID NUMBER,
SIGNAL_NAME VARCHAR2(64 BYTE)
);
INSERT INTO SIGNALS(SIGNAL_ID, SIGNAL_NAME) VALUES (1,'AAA');
INSERT INTO SIGNALS(SIGNAL_ID, SIGNAL_NAME) VALUES (2,'BBB');
INSERT INTO SIGNALS(SIGNAL_ID, SIGNAL_NAME) VALUES (3,'CCC');
CREATE TABLE SIGNAL_DATA
(
DATETIME DATE,
SIGNAL_ID NUMBER,
VALUE NUMBER
);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,56);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS'),1,57);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 02:00:00','YYYY-MM-DD HH24:MI:SS'),1,58);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 03:00:00','YYYY-MM-DD HH24:MI:SS'),1,59);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,871);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS'),2,987);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 02:00:00','YYYY-MM-DD HH24:MI:SS'),2,991);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 03:00:00','YYYY-MM-DD HH24:MI:SS'),2,1024);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,0.5);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 01:00:00','YYYY-MM-DD HH24:MI:SS'),3,0.76);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 02:00:00','YYYY-MM-DD HH24:MI:SS'),3,-1.53);
INSERT INTO SIGNAL_DATA(DATETIME,SIGNAL_ID,VALUE) VALUES(TO_DATE('2012-01-01 03:00:00','YYYY-MM-DD HH24:MI:SS'),3,5);
|
|
| | |
| Re: Dynamic Columns based on Query(Oracle10G 10.2.0.3.0) [message #540754 is a reply to message #540737] |
Wed, 25 January 2012 09:45 |
syakobson
Messages: 771
Registered: January 2010 |
Senior Member |
|
|
Ramses800 wrote on Wed, 25 January 2012 09:38It is a compiled binary from third party vendor. It accepts standard queries as input(meaning that I write a query and the client executes it). It itself cannot execute dynamic SQL but it is possible to call a Oracle function/procedure that accepts parameter from the client.
What if you modify Michel's function to return generated SQL statement text. Then pass:
SELECT function_name
FROM DUAL
to vendor binary. It will return result back to you, right? Then pass that result as SQL as to vendor binary.
SY.
|
|
| | | | | | | | | |
| Re: Dynamic Columns based on Query(Oracle10G 10.2.0.3.0) [message #541060 is a reply to message #540870] |
Fri, 27 January 2012 06:24 |
|
Ramses800
Messages: 12
Registered: January 2012
Location: Sweden |
Junior Member |
|
|
Michel Cadot wrote on Thu, 26 January 2012 14:12or you can concatenate all fields in a single string,
Yes, we have tried something along those lines
select datetime,
max(substr(sys_connect_by_path (value, ';'),2)) values_by_id
from (
select signals.signal_id,
to_char(h.datetime,'yyyy-mm-dd hh24:mi:ss') datetime,
h.value,
row_number() over (partition by h.datetime order by signals.signal_id) rn
from signals v, signal_data h
where
signals.signal_id = signal_data.signal_id
and h.datetime between caltosys(sysdate-1) and caltosys(sysdate)
)
start with rn = 1
connect by rn = prior rn + 1
and prior datetime = datetime
group by datetime
order by datetime
and getting this
DATETIME VALUES_BY_ID
2012-01-01 00:00:00 56;871;0.5
2012-01-01 01:00:00 57;987;0.76
But getting from here into the desired output have yet eluded us....
[Updated on: Fri, 27 January 2012 06:26] Report message to a moderator |
|
| | | | | | |
Goto Forum:
Current Time: Thu Feb 23 04:14:56 CST 2012
Total time taken to generate the page: 2.51090 seconds
|
] 
Members
Search
Help
Register
Login
Home
