Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: No true Crosstab Query in Oracle???

Re: No true Crosstab Query in Oracle???

From: Gavin John Fowler <Gavin_Fowler_at_hotmail.com>
Date: 28 Dec 2002 19:09:48 -0800
Message-ID: <5aa05620.0212281909.7f2a4fc7@posting.google.com>


Its not conceptually difficult but can be quite tricky if I understand your problem correctly.
You need to cope with a variable number of 'header' columns in the output - yes ? (Remember that there may well be an application receiving this output and this app may expect a fixed number of coulumns .. so you have to deal with that too ...)

This is one way.
Each time your corsstabulation report (cause that is what it is) is run you need to get the 'headers' from some kind of 'SELECT DISTINCT <header_cols, header_identifiers etc> from <tables> WHERE < clause> etc etc
Oracle 'dynamic' SQL I am a little new to, but in Sybase & MS SQL I would build a string representing a correlated sub-query over the same join as above (in <tables> giving me one column for every 'header' (or header_identifier) using these 'headers' as parameters in the WHERE clause. (In Sybase or MS SQL I can use a CURSOR for this but you could conceivably use another looping construct) The select will, of course, have an aggregation function such as count etc.

If your front-end app is expecting 'N' columns then you must ensure that 'N-number('headers'_found)' 'inert' columns are added by concatenating any suitably formatted strings representing missing columns (eg '''N/A''').

In my experience cross-tabulation in SQL is messy when you cannot predict the number of columns returned and there is no way to do this with 'static' SQL (I believe it is possible to prove this .. see some of Joe Celko's papers)

As I am new to Oracle please take this as an idea only, forgive me if I overlook something obvious.

Yours,
Gavin
"John W" <John.Wisnieff_at_oracle.com> wrote in message news:<KD6J9.9$e14.159_at_news.oracle.com>...
> Ethel, do you have any sample code for this?
> Thanks,
> John
>
> "Ethel Aardvark" <bigjobbies_at_hotmail.com> wrote in message
> news:1a8fec49.0211270540.4fc248dc_at_posting.google.com...
> > Since I don't think it can be done in SQL, it looks like a job for
> > PL/SQL (or some front-end tool, depending on your environment).
> >
> > EA
> >
> > "John W" <John.Wisnieff_at_oracle.com> wrote in message
> news:<pkNE9.4$Ws2.35_at_news.oracle.com>...
> > > Ted,
> > > I asume you are referring to the "pivot tables" article. That article is
> > > about creating news rows and not about creating new columns. I am
> looking
> > > for sql code that will create a variable number of columns on the fly.
> > > Thanks,
> > > John
> > >
> > >
> > > "John W" <John.Wisnieff_at_oracle.com> wrote in message
> > > news:PcuE9.6$4B4.179_at_news.oracle.com...
> > > > Ted, thanks, I'll review the article.
> > > >
> > > > "Ted McCabe" <theom_at_gte.net> wrote in message
> > > > news:HjAD9.5818$mL2.5112_at_nwrddc01.gnilink.net...
> > > > > John:
> > > > > The September/October 2002 Oracle Magazine has a "how to" in it.
> > > > > http://technet.oracle.com/oramag/content.html
> > > > > HTH
> > > > > tED
> > > > > "John W" <John.Wisnieff_at_oracle.com> wrote in message
> > > > > news:T7AD9.16$lF3.141_at_news.oracle.com...
> > > > > > I need to do a crosstab query in Oracle where the values from one
> field
> > > > > > become the column headers in the final query result.
> > > > > > An example of this is taking a table that looks like:
> > > > > >
> > > > > > sample_id, test_desc, result
> > > > > > 1, Moisture, 23.4
> > > > > > 1, Carbon, 32.5
> > > > > > 1, Sulfur, .0023
> > > > > > 2, Moisture, 54.2
> > > > > > 2, Carbon, 12.2
> > > > > > 2, Sulfur,
> > > > > > 3, Sulfur, .023
> > > > > >
> > > > > > and returning a result that looks like this:
> > > > > >
> > > > > > sample_id, Moisture, Carbon, Sulfur, ...
> > > > > > 1, 23.4, 23.4, 32.5, .0023
> > > > > > 2, 54.2, 12.2,
> > > > > > 3, , , .023
> > > > > >
> > > > > >
> > > > > > However I find no crosstab functionality in Oracle. All I see in
> researching
> > > > > > this is a technique to manually create a DECODE statement for a
> fixed
> number
> > > > > > of columns. Since the number of columns for this query can vary
> this
> option
> > > > > > will not work and this technique is not a true crosstab query.
> Does
> anyone
> > > > > > know of a way to do a crosstab query in Oracle similar to those
> that
> > > can
> > > be
> > > > > > done by desktop databases such as Paradox or MS Acesss?
> > > > > > Thanks
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
Received on Sat Dec 28 2002 - 21:09:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US