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