Re: Aggregations

On Feb 9, 6:38 am, "AlexK" <alex..._at_hotmail.com> wrote:
> Hi All,
>
> I'm trying to aggregate some data and I was hoping for some insight.
> Basically I have some data like the following:
>
> Key Column1 Column2
> 123 Y null
> 123 null Y
> 456 null Y
>
> I'd like to aggregate the data by the key field and have the output
> turn out like the following:
>
> Key Column1 Column2
> 123 Y Y
> 456 null Y
>
> Would anyone have some insight on how I can accomplish this? Thanks
> in advance.
>
> Alex

Hi,

maybe the following hints will be helpful for you:

alek_at_pdmdb> create table agg (key integer, column1 varchar2(1), column2 varchar2(1));

Table created

alek_at_pdmdb> insert into agg values (123, 'Y', null);

1 row inserted

alek_at_pdmdb> insert into agg values (123, null, 'Y');

1 row inserted

alek_at_pdmdb> insert into agg values (456, null, 'Y');

1 row inserted

alek_at_pdmdb> select key, max(column1), max(column2) from agg group by key;

                                    KEY MAX(COLUMN1) MAX(COLUMN2)
--------------------------------------- ------------ ------------
                                    123 Y            Y
                                    456              Y

alek_at_pdmdb> select distinct key, first_value(column1) over (partition by key order by column1), first_value(column2) over (partition by key order by column2) from agg;

                                    KEY FIRST_VALUE(COLUMN1)OVER(PARTI

--------------------------------------- ------------------------------
------------------------------
                                    123 Y
Y
                                    456

Cheers! Received on Fri Feb 09 2007 - 01:23:20 CST