View for Cross Tab Report
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: View for Cross Tab Report

  1. #1
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    Could anyone make a suggestion as to how to correctly produce a view for a crosstab report? I can't find any way to "pivot" the results, and have only been able to produce rows based on rollup etc, which then requires rather complicated code to manually pivot the results for output to a html table.

    For example, I have the following in one table, AFLPrice:

    Team Bookmaker Price
    ========================
    Geelong IAS 1.45
    Geelong SBA 1.50
    Geelong GSB 1.47
    Melbourne IAS 2.35
    Melbourne SBA 2.20
    Melbourne GSB 2.25

    I need to express this in a view with output:

    Team IAS SBA GSB
    ===============================
    Geelong 1.45 1.50 1.47
    Melbourne 2.35 2.20 2.25

    Note that I do not know in advance how many Bookmakers I will have, nor therefore how many columns in the final crosstab.

    I didn't think this would be such a challenge, but I just can't seem to get this type of view successfully.

    If you have any suggestion for me, I would most appreciate it!


    MTIA, Max Hugen

    Hugen Enterprises Pty Ltd
    207 Flood St, Leichhardt NSW 2040, Australia
    Tel: 02 9560 3061
    http://www.hugen.com.au
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hi

    search in asktom.oracle.com

    there are 4 or 5 threads about pivoting queries

  3. #3
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    Thanks pando

    I will go through the explanations there, and see if I can understand it.

    Cheers, Max

  4. #4
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Unhappy

    I tried one of the examples at the asktom site, using types and arrays, but the results are still pretty awful, requiring heaps of parsing (I think). The output was, for one row:

    melbourne
    AFL_BOOKMAKERPRICEARRAY(AFL_BOOKMAKERPRICE('canbet', NULL), AFL_BOOKMAKERPRICE('globalsportsbet', 7.75), AFL_BOOKMAKERPRICE('ias', 7.75), AFL_BOOKMAKERPRICE('sportingbet', NULL))

    Yuck!

    It also seemed rather slow... is there a significant performance hit using types and arrays to do this job?

    The alternative may be to pre-determine the number of Bookmakers available, and construct the sql dynamically somehow, using the old decode/grouping technique...

    As an Oracle novice, I am still surprised that this is so, well, awkward, at performing what I'd call a pretty common crosstab query.

    If anyone has a 'tidier' &/or faster(?) way to do this, I'd really appreciate hearing from you!

    Many thanks, Max
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  5. #5
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    Thanks pando

    I will go through the explanations there, and see if I can understand it.

    Cheers, Max

  6. #6
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Unhappy Still Stuck!

    Sorry, don't know how I managed to repost an earlier message???

    I'm still have major problems with this.... what do others do when they need to create a cross tab report? Any suggestions please?

    MTIA
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

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