Re: Need help writing SQL Query

On Sat, 19 Jul 2003 14:21:50 -0700, Daniel Morgan <damorgan_at_exxesolutions.com> wrote:

>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)
>
>

Your answer is superficial, as often seen.....

Your answer requires that all column values are known ( = static). As soon another parent row is inserted into TableA, you need to rewrite the statement......furthermore, your suggestion does not bring the OP closer to a solution. Was the "teacher" just eager to be right in....something?

No. I am not going to discuss with you further.

• Kenneth Koenraadt