Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: No true Crosstab Query in Oracle???
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