DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Crosstab/Pivot Tables

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

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

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