Re: Cross Tab Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 11 Dec 2009 08:29:38 -0800 (PST)
Message-ID: <af541b48-505c-4675-8679-86976ca58aa3_at_j4g2000yqe.googlegroups.com>



On Dec 11, 10:20 am, kou vang <gqko..._at_gmail.com> wrote:
> I can get this query to put the dataset together, but would love to
> get it into a cross tab.  I don't know how to work with multiple CASE
> statements to do this?  I want the columns to be the CASE results:
> Aquatic C, Aquatic R, Other and the Value to be ATTAINMENT_Desc.  I
> think I'm pretty close.  Thanks.
>
> SELECT
> ASSESS.LAKE_AUID.LAKE_ID,
> ADB.ASSESSMENT_UNITS.CYCLE,
> ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
> (CASE ADB.Use_Lut.USE_ID
>  WHEN 523 THEN 'Aquatic Consumption'
>  WHEN 552 THEN 'Aquatic Consumption'
>  WHEN 555 THEN 'Aquatic Consumption'
>  WHEN 565 THEN 'Aquatic Consumption'
>  WHEN 553 THEN 'Aquatic Recreation'
>  WHEN 556 THEN 'Aquatic Recreation'
>  WHEN 566 THEN 'Aquatic Recreation'
>  ELSE 'Other'
> END) AS ATT_DESC
>
> Kou

There are certainly other solutions, but I would be tempted to do something like this using DECODE:
SELECT
ASSESS.LAKE_AUID.LAKE_ID,

ADB.ASSESSMENT_UNITS.CYCLE,
ADB.ATTAINMENT_LUT.ATTAINMENT_DESC,
DECODE(ADB.Use_Lut.USE_ID,
       523,ATTAINMENT_Desc,
       552,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       565,ATTAINMENT_Desc,
       NULL) AS CONSUMPTION,
DECODE(ADB.Use_Lut.USE_ID,
       553,ATTAINMENT_Desc,
       556,ATTAINMENT_Desc,
       555,ATTAINMENT_Desc,
       566,ATTAINMENT_Desc,
       NULL) AS RECREATION,
DECODE(ADB.Use_Lut.USE_ID,
       523,NULL,
       552,NULL,
       555,NULL,
       565,NULL,
       553,NULL,
       556,NULL,
       555,NULL,
       566,NULL,
       ATTAINMENT_Desc) AS OTHER

You can see how DECODE works by reviewing the following: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm

Note that the DECODE syntax above may be re-written using CASE syntax.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 11 2009 - 10:29:38 CST

Original text of this message