Re: two contact aggregate function not same order

From: NOVA <nova1427_at_gmail.com>
Date: Mon, 1 Jun 2009 23:07:38 -0700 (PDT)
Message-ID: <f5b762ee-bf5e-4618-9669-c5468bd42082_at_z9g2000yqi.googlegroups.com>

On Jun 2, 8:14 am, Peter Nilsson <ai..._at_acay.com.au> wrote:
> NOVA wrote:
> > I'm try to make aggregate function for contact the the rows as you see
> > below.
>
> You may not need a function.
>
> <snip>
>
>
>
>
>
> > create table agg_test (
> >   a number,
> >   b varchar2(5)
> > );
>
> > insert into agg_test values (1,'a','aa');
> > insert into agg_test values (2,'A','AA');
> > insert into agg_test values (1,'b','bb');
> > insert into agg_test values (1,'c','cc');
> > insert into agg_test values (2,'B','BB');
> > insert into agg_test values (2,'C','CC');
> > insert into agg_test values (1,'d','dd');
> > insert into agg_test values (2,'D','DD');
>
> > select  a, agg_concat(b) bb, agg_concat(c) cc
> > from AGG_TEST
> > group by a
>
> > the problem is: coloumn bb and cc not same order, it give me this
> > result:
> > A  BB      CC
> > 1  "a,b,d,c"     "aa,bb,dd,cc"
> > 2  "A,B,D,C"     "AA,CC,BB,DD"
>
> > it should by like this
>
> > A  BB      CC
> > 1  "a,b,d,c"     "aa,bb,dd,cc"
> > 2  "A,B,D,C"     "AA,BB,DD,CC"
>
>   with
>     agg_test as
>     (
>       select 1 a, 'a' bb, 'aa' cc from dual union all
>       select 2 a, 'A' bb, 'AA' cc from dual union all
>       select 1 a, 'b' bb, 'bb' cc from dual union all
>       select 1 a, 'c' bb, 'cc' cc from dual union all
>       select 2 a, 'B' bb, 'BB' cc from dual union all
>       select 2 a, 'C' bb, 'CC' cc from dual union all
>       select 1 a, 'd' bb, 'dd' cc from dual union all
>       select 2 a, 'D' bb, 'DD' cc from dual
>     )
>   select a, bb, cc
>     from (
>            select a,
>                   regexp_replace(
>                     sys_connect_by_path(bb, ','), '^,', ''
>                   ) bb,
>                   regexp_replace(
>                     sys_connect_by_path(cc, ','), '^,', ''
>                   ) cc,
>                   rn,
>                   ct
>              from (
>                     select t.*,
>                            row_number() over
>                              (partition by a order by bb) rn,
>                            count(1) over
>                              (partition by a) ct
>                       from agg_test t
>                   )
>             start with rn = 1
>           connect
>                by prior a = a
>               and prior rn + 1 = rn
>          )
>    where rn = ct
>    order by a
>
> --
> Peter- Hide quoted text -
>
> - Show quoted text -

Thousands of thanks.

I like this solution so much. Received on Tue Jun 02 2009 - 01:07:38 CDT