Cross Tab Help

[kou vang]

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

[Charles Hooper]

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.

[kou vang]

> 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

[Charles Hooper]

> 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

Without seeing the entire SQL statement, I would say that Oracle is
also expecting you to group on the column ADB.Use_Lut.USE_ID and
possibly ATTAINMENT_Desc.

To work around this, slide your SQL statement into an inline view and
then perform the group outside the inline view. For example, if your
SQL statement currently looks like this:

FROM
T1;

You would change it like this:
SELECT
LAKE_ID,
CYCLE,
ATTAINMENT_DESC,
SUM(CONSUMPTION) CONSUMPTION,
SUM(RECREATION) RECREATION,
SUM(OTHER) OTHER
FROM
(SELECT

FROM
T1)
GROUP BY
LAKE_ID,
CYCLE,
ATTAINMENT_DESC;