Re: No true Crosstab Query in Oracle???

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 Wed Nov 27 2002 - 07:40:53 CST