Re: No true Crosstab Query in Oracle???

Gavin John Fowler wrote:

> 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >

Cross-tabulation is easily accomplished using DECODE and CASE. Just alias the resulting columns.

To handle an unknown number of columns either use a default clause or use dynamic SQL.

Daniel Morgan Received on Sun Dec 29 2002 - 23:48:17 CST