# 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)

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
)
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

Original text of this message