Re: Creating A Crosstab Query

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: 1997/12/04
Message-ID: <666s3b$as8@yobi.sierra.com>#1/1

For those of you who haven't used MS-Access, a cross-tab query takes arbitrary data from fields in ROWS and "pivots" them into column headings.

For example, given a table with these columns:

a_field, a_date, some_data

with data like:

Myshoe, 12/31/97, 10
Mybelt, 11/31/97, 32
Myhat, 10/31/97, 9

one could use MS-Access to generate a crosstab query which would look like this:

                            10/31/97           11/31/97             12/31/97
Myshoe                     0                      0
10
Mybelt                        0                     32
0
Myhat                         9                      0

On Oracle using sql, the query would have to look like:

select a_field,

           decode(a_date,'10/31/97',some_data) '10/31/97',
           decode(a_date,'11/31/97',some_data) '11/31/97',
           decode(a_date,'12/31/97',some_data) '12/31/97'

from a_table;

The only problem here is that you must know and specify ALL POSSIBLE COLUMN NAMES (i.e., 10/31/97, 11/31/97, etc) which obviously is not practical.

The way I do this is to perform a group by select query to obtain a list of the column names, then MANUALLY build the decode statements.

Abeeda_Mohammed_at_xn.xerox.com wrote in message <880048955.9646_at_dejanews.com>...
>RDBMS Version: 7.3.2.3.1
>Operating System: NT 4.0
>Product: PL/SQL 2.3.2.3
>Product Version: 2.3.2.3
>
>
>The following is the structure of a table :
>inventory_item_id
>Organization_id
>cost_type_id
>summary_resource_id
>description
>item_cost
>
>I want to write a query so that I can get the sum of item_cost for each
>description, i.e the description should appear as a column. I am not sure
>what the description is going to be so I cannot write a SQL stmt. with
>sum(decode.. function to perform this crosstab query.
>
>Is there a way out?
>
>thanks in advance,
>
>Abeeda
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Thu Dec 04 1997 - 00:00:00 CST