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