Re: Crosstab, TRANSFORM in Oracle

From: Mike Hackett <sharmike_at_voicenet.com>
Date: 1997/09/29
Message-ID: <60plj8$naq$1@news3.voicenet.com>#1/1

Microsoft Access has a feature which dynamically creates columns from row values to create a crosstab.
As far as I know, Oracle does not have such a feature. You have a few options.
If you know ahead of time all the possible values of a column, you can manually code the definition for each column of the crosstab. Assuming a a table with a product and a region column, you might code a sales by product and region using the decode function:

select

  sum(decode (region, 'North', sales, 0)) North,
  sum(decode (region, 'South', sales, 0)) South,
  sum(decode (region, 'East', sales, 0)) East,
  sum(decode (region, 'West', sales, 0)) West

from
  sales
group by
  product

There may be some way to build something like this dymanically using the DBMS_SQL package. You also might want to see if your reporting tool can do something like this automatically. If you tried to run an Access query against an attached/linked Oracle table, Jet would probably be smart enough (I won't guarantee this) to ask Oracle for a "select sum(sales) from sales group by product, region" and then do the crosstab from the result. I think Oracle Reports might do this as well.

>is there a reliable way of doing crosstab (OR TRANSFORM / PIVOT) queries in
>Oracle like there is in Access??
>
>How is this done??
>
>Thanks
>
>
>Oscar
>
Received on Mon Sep 29 1997 - 00:00:00 CDT