Re: Cross Tab Help

From: kou vang <gqkou79_at_gmail.com>
Date: Fri, 11 Dec 2009 10:52:20 -0800 (PST)
Message-ID: <12f645aa-c569-4409-9846-f942ef4abe0a_at_g26g2000yqe.googlegroups.com>

On Dec 11, 10:29 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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/functi...
>
> 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.- Hide quoted text -
>
> - Show quoted text -

I almost got everything to work with the DECODE help, except how do I do a GROUP BY or DISTINCT? When I try a GB it gives me an error, saying not a GB clause?

GROUP BY ASSESS.LAKE_AUID.LAKE_ID, ADB.ASSESSMENT_UNITS.CYCLE Received on Fri Dec 11 2009 - 12:52:20 CST