Re: Need help writing SQL Query

"Enzo Maini" <maini_at_earthlink.net> wrote

> Is there a way to write a query such that it returns a row like this in
> SQLPlus
>
> ColA ColB "Freight Charge" "Weight"
> ------- ------- ---------------------------- -----------------
> DHL 2000 100 70
> UPS 2500 100 55
>
> Esentially I need a cross-tab and some how merge that with a regular
> standard select

Yes and no.

The no part - not possible if the number of columns to be returned is variable (i.e. if ColX contains unknown values up front). The SQL projection part needs to know _what_ columns to return in order to crosstab.. and if that is the case, the answer is yes.

For example:
SELECT
  ColA,
  ColB,
  DECODE( ColX, 'Freight Charge', ColZ, NULL ) FREIGHT_CHARGE,   DECODE( ColX, 'Weight', ColZ, NULL ) WEIGHT FROM ..table A & B joined...

As you can see, you need to know the values of COlX up front in order to code the SQL. If you don't know them, then you cannot construct the SQL projection. You also need to keep in mind that there is a limit on the number of columns that can be projected.

IMO a better approach is using OO in how the data is acessed.

--
Billy