View for Cross Tab Report

[maxhugen]

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

[pando]

hi

search in asktom.oracle.com

there are 4 or 5 threads about pivoting queries

[maxhugen]

Thanks pando

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

Cheers, Max

[maxhugen]

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

[maxhugen]

Thanks pando

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

Cheers, Max

[maxhugen]

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