DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2000
    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?
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    May 2001
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.