Something like: invert rows to columns in a query result
PatchaJan 27 2011 — edited Jan 27 2011Hi all,
sorry for the strange thread title, but didn't know how to title it better. :)
My situation is the following:
I have a table with lot of data (almost 18000000 records!) and it stores some reporting values about some Devices behaviour.
I have 5 days report data for every 30 mins... something like:
DAY, HOUR, DEVICE, VALUE
Now I need to fill a Lines Chart in ORACLE APEX for a single DEVICE (so is implied I'll use a "WHERE" condition on the DEVICE name).
Y axis should be for VALUE values and X axis should be HOUR values (aka: time every 30 mins).
The problem is I wish to write a line for each day in the same Chart, but ORACLE APEX pretends the resulset should be formatted following:
HOUR, DAY1_VALUES, DAY2_VALUES, DAY3_VALUES, DAY4_VALUES, DAY5_VALUES
How to get it with a single query?
Why forcely a single query? Because a query with or without "DAY" filter (where DAY = ...) takes always from 20 to 25 seconds... so if I could resolve with a single query I would save 80 seconds!
PS:
Actually I have a table wich just stores involved DAYS date (5 rows, 1 per DAY).
Infact I could develop a PL/SQL procedure which logically would work like that:
for day in (select DAY ... )
for values in (select HOUR label, VALUE ... where DAY = day.DAY)
then filling xml for each day...
But it would execute 5 queries with 20 seconds duration for each... and wouldn't resolve my problem...
Thank you all!