Start a new topic

could a pivot function be added ?

[This topic is migrated from our old forums. The original author name has been removed]

I often have queries like (query 1): select sysdate, tablespace_name, sum(bytes) bytes from dba_data_files; that I want to use to do some monitoring. The result is sysdate, tablespace_name, bytes -------- ------------- ----- 20051128 13, SYSTEM, 50000000 20051128 13, USERS, 1000000 20051128 13, TOOLS, 100000 The dbVis monitoring tool wants me to code this query as (query 2): select sysdate, (select sum(bytes) from dba_data_files where tablespace_name = 'SYSTEM' group by tablespace_name) as system, (select sum(bytes) from dba_data_files where tablespace_name = 'USERS' group by tablespace_name) as users, (select sum(bytes) from dba_data_files where tablespace_name = 'TOOLS' group by tablespace_name) as tools from dual giving: sysdate, system, users, tools 20051128 13, 50000000,1000000,100000 The data is the same but the query is a little different. Imagine how this would look like with 20 tablespaces of more, or even worse, when a little more computation has to be made. I think this can be easier if dbVis had some pivot function that has the query (query 1) as input and generates (query 2) from it and executes it. This would make life lots easier by having the computer do what I am now forced to do by hand. I hope this request will be taken into consideration. best regards, Ronald.

[This reply is migrated from our old forums.]

Re: could a pivot function be added ?
Ronald, I agree that the current [b]columns = chart series[/b] strategy is not always how the data is organized. We have on the todo list to add a way to convert the original data so that rows instead become series. Thanks! Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: could a pivot function be added ?
This should not be too difficult. Most queries I see are in the form of timestamp, (name, value) ... There could be an extra challange when you want to be able to handle a varying list of names during the same monitoring session. Things like that can happen when we add a tablespace in the given example, or drop one. I also noticed that dbVis does handle an extra column but fails to cope with the NULL values that are placed in the new columns for the already existing rows in the grid. When can we expect a version with this feature in it to be ready for download ? ;-) Ronald.
[This reply is migrated from our old forums.]

Re: could a pivot function be added ?
Ronald, You mention: [i]I also noticed that dbVis does handle an extra column but fails to cope with the NULL values that are placed in the new columns for the already existing rows in the grid.[/i] In what way does DbVisualizer fail to handle null values? (A null appear as a hole value in the chart). Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: could a pivot function be added ?
I would appreciate this functionality as well! It would save me using Oracle SQL Developer for charting (or the roundtrip to Excel). +1 for rows as series ... Regards, Chris
[This reply is migrated from our old forums.]

Re: could a pivot function be added ?
Chris, Your vote has been added. Regards Roger