-
Do any of the new analytical functions in Oracle 8i perform a crosstab/pivot table query?
I have a table (disk_utilization) that looks like:
sample_dt date
device_cd varchar2(10)
util_pct number(6,2)
The data in this table looks like:
sample_dt device_cd utl_pct
01/01/2001 sd0 50
01/01/2001 sd1 25
01/01/2001 sd0 0
01/01/2001 sd1 25
01/01/2001 sd0 25
01/01/2001 sd1 25
01/02/2001 sd0 50
01/02/2001 sd1 30
01/02/2001 sd2 25
01/02/2001 sd0 20
01/02/2001 sd1 30
01/02/2001 sd2 25
01/02/2001 sd0 20
01/02/2001 sd1 30
01/02/2001 sd2 25
I want to get something like:
sample_dt sd0 sd1 sd2
01/01/2001 25 25 0
01/02/2001 30 30 25
I am looking for a generic query solution. I know that I can write a stored procedure to generate the query, but that would take some time. I know that to solve this exact problem, I could write a query with hardcoded values, but as I say, I am looking for a generic solution where the number of device_cd's will vary.
Any suggestions?
-
Here is a short SQL*PLUS script that should do the trick.
set head off feedback off verify off term off
spool disk_util.sql
select 'select sample_dt' from dual;
select distinct
',min(decode(device_cd,'''||device_cd||''',utl_pct)) '||device_cd
from disk_utilization;
select ' from disk_utilization group by sample_dt;'
from dual;
spool off;
set head on term on
@disk_util.sql
