Re: two contact aggregate function not same order

From: Peter Nilsson <airia_at_acay.com.au>
Date: Mon, 1 Jun 2009 22:14:35 -0700 (PDT)
Message-ID: <8866c106-4e8b-4832-b3d1-09fb223f1c22_at_a36g2000yqc.googlegroups.com>

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

Received on Tue Jun 02 2009 - 00:14:35 CDT