Re: Need help writing SQL Query

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
>
>
Received on Sat Jul 19 2003 - 14:35:30 CDT