Re: Need help writing SQL Query

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 19 Jul 2003 14:21:50 -0700
Message-ID: <3F19B66E.B7FB2D46@exxesolutions.com>

Kenneth, Koenraadt wrote:

> Hi Enzo,
>
> The bad news :
>
> There is no way that you can turn a column value into a column itself
> in a SQL statement.
>
> The good news :
>
> In 99,9% of the cases, you don't need to do that! You can almost
> always get the application to deliver the desired result by means of
> SQL or PL/SQL without turning values into columns.
>
> The sober facts :
>
> If you MUST do it, you can use write a PL/SQL application which e.g.
> retrieves row values and dynamically creates tables and/or views with
> the desired column names. It's cumbersome and, as said, most likely
> unnecessary.
>
> - Kenneth Koenraadt
>
> On Sat, 19 Jul 2003 03:38:12 GMT, "Enzo Maini" <maini_at_earthlink.net>
> wrote:
>
> >I have two tables,
> >
> > Table A (
> > TabAID number
> > ColA varchar,
> > ColB number
> > )
> >
> > Table B (
> > TabBID number,
> > TabAID number,
> > ColX varchar,
> > ColZ varchar
> >)
> >
> >Table A Values:
> >
> >TabAID ColA ColB
> >---------- ------- --------
> >1001 DHL 2000
> >1002 UPS 2500
> >
> >Table B Values:
> >
> >TabBID TabAID ColX ColZ
> >----------- ----------- -------- ------------
> >5000 1001 Freight Charge 100
> >5001 1001 Max Weight 70
> >5002 1002 Freight Charge 100
> >5003 1002 Max Weight 55
> >
> >
> >ColX in Table B is really a column name and ColZ holds the value. Note,
> >that the values
> >in Table B will grow over time meaning more columns will be returned
> >
> >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
> >
> >Thanks
> >
> >

Sorry ... but that is nonsense. Pivot tables are a routine use of the DECODE function as clearly shown by the following:

SELECT program_id,

              DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
              DECODE(customer_id, 'DAL', 'DAL') DELTA,
              DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
              DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING

FROM airplanes;

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Jul 19 2003 - 16:21:50 CDT